Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?
share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

http://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

share|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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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