Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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

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


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.