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?
Ben Curthoys
  • 153
  • 1
  • 6

1 Answers1

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

Community
  • 1
Greg Askew
  • 35,880
  • 5
  • 54
  • 82
  • 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