4

In my nightly maintenance plan, I was doing everything - reorganising and rebuilding indexes, updating statistics, etc. There's an hourly maintenance plan that does a transaction log backup.

I noticed that the transaction log backup immediately after the nightly rebuild was huge - and a little research told me that was because rebuilding indexes etc were logged operations.

I don't want to burden my backup system by backing up logs of index rebuilds. Therefore, I plan to change by Nightly plan to:

  1. Rebuild Indexes, Update stats, etc
  2. DUMP TRANSACTION WITH NO_LOG (oh, which is apparently called BACKUP LOG test WITH TRUNCATE_ONLY these days)
  3. Shrink log files
  4. Do full database backup

Questions:

  1. Is there something wrong with this plan?
  2. What's the best way to include the log truncation in a maintenance plan?
  3. I just read that including both Reorganise and Rebuild indexes is redundant. How should I decide which is the right one to use in a nightly plan?
2

Yes, doing both an Organize and Rebuild is redundant. You should do one or the other, but not both.

On large tables, I would not do an automatic rebuild. I would run a custom script that determines if the fragmentation exceeds a threshold, and do a rebuild only if it exceeds the threshold. Some people use 40% for the rebuild percentage.

Script to appropriate rebuild/reorganize database indexes
https://blogs.msdn.com/b/joaol/archive/2008/01/28/script-to-rebuild-and-reorganize-database-indexes-sql-server-2005.aspx

https://stackoverflow.com/questions/7579/reorganise-index-vs-rebuild-index-in-sql-server-maintenance-plan

http://www.sql-server-performance.com/2007/rebuilding-indexes

http://www.tekrhythm.com/2012/01/21/sql-script-to-rebuild-indexes-more-intelligently

| improve this answer | |
  • Well, if there's nothing obviously wrong with doing a rebuild/reorganise, then dumping the transaction log, and then doing a full backup and carrying on, then that's what I'll do. – Ben Curthoys Jul 21 '12 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.