Solved

Housekeeping on SQL Server 2008 R2

Posted on 2014-09-16
12
421 Views
Last Modified: 2014-09-17
Beside the "Shrink Database Task" on SQL Maintenance Plan, what other tasks do I normally need ? Do I need to purge any job log ?

Tks
0
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 250 total points
ID: 40325282
You should not perform the Shrink Database task. This causes fragmentation of you indexes and will cause performance problems.

At minimum, database backups and transaction log backup (for databases with a recovery model of Full or Bulk). You can also look at defragmenting or re-building your indexes on a periodic basic.

There are probably other tasks to consider and the other more seasoned experts here can assist.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40325376
Shrink Database is a task that you shouldn't do for a basis maintenance plan. You should even avoid it as Shaun explained to you.
Maintenance Plans are running Backups strategy and regular reindexes.
Jobs logs can be purged by configuring the SQL Server Agent.
0
 

Author Comment

by:AXISHK
ID: 40325415
When should I use Shrink Database Task ?

My SQL log is keep growing even after daily backup.  Where can I configure to  truncate the log after backup ?

Beside, where can I configure to purge the SQL job log ? Tks

Tks
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40325435
In a perfect world you wouldn't have the need to shrink database.
Which type of backups are you performing? For truncate the log you need to run transactional backups and depending on the use of the database it's almost sure that you need to run a tlog backup more than once by day (4h in 4h, 2h in 2h, ...).
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40325477
One other thing you can consider doing is to not use the built-in maintenance plans and use a tested solution such as what is found at this site: https://ola.hallengren.com/.

The scripts that this person provides are free and provide a more granular control over four areas of maintenance: Backup, Index Rebuilds/Defrags, DB Integrity Check, and Update Statistics.
0
 

Author Comment

by:AXISHK
ID: 40325488
Only schedule database backup at night. Can I just add a maintenance task following the backup job to truncate the log ? Tks
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40325524
Depending on your backup plan, you could run a full backup weekly, differential backups nightly (minus the night of the full backup) and then transaction log backups during the day. If you need to restore your database, you would need the weekly full backup, the differential backup from the night in question, and any transaction log backups for that day, depending on why your are restoring your database.

If you are not concerned with the backup file size, you can drop the differential backup for full nightly backups.

The transaction log backups are needed to keep the transaction log file from growing unchecked.
0
 

Author Comment

by:AXISHK
ID: 40325588
For full SQL backup at night, it will backup database and transaction log. I can use it to recover the full database, correct ?

If yes, I can simply add a additional task "DBCC SHRINKFILE (MySQLDatabase_Log, 50);" correct ?

Tks
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40325639
Please forget the shrinkfile. As I understood you have only a full backup running daily. If you want to maintain that full backup it's ok but you need to add at least transaction log backups.
What you going to do if your database crashes during the day? You can only recover the day before and all the changes made during that day will be lost.
0
 

Author Comment

by:AXISHK
ID: 40327014
Need to shrink the transaction log as the SQL transaction is over 100GB... I want it to setup as 50MB. Can I issue the command in SQL maintenance job after full SQL Backup ?

Tks
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40327372
Like I said before, you shouldn't need to shrink databases but if you do I recommend that you do it in an ad-hoc solution instead of a permanent one, i.e. run it only once instead of including this task in a recursive job.
0
 

Author Closing Comment

by:AXISHK
ID: 40327499
Tks
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
(sql serv16)ssis 2016 question/check 1 126
sql query help 15 67
Applying Roles in Common Scenarios 3 43
What does "Between" mean? 6 46
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question