Solved

Housekeeping on SQL Server 2008 R2

Posted on 2014-09-16
12
444 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 51

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 51

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 51

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 51

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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