Home / server / Questions / 409872
Accepted
Ben Curthoys
Asked: 2012-07-22 03:13:50 +0800 CST

SQL Server Maintenance Plan: Rebuild Indexes and Transaction Log Backup File Size

  • 772

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?
log-files

1 Answers

  1. Best Answer

    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

    • 2