As part of SQL server database maintenance, the indexes have to be defragmented on regular basis by rebuild or reorganize for consistant performance. Most of the time, the index maintenance jobs fail in the middle for large databases due to insufficient storage in log files and the entire rebuild process has to be restarted. This consumes a lot of time and also causes prolonged performance issues.
To tackle this, I have scripts which could resume from where it stopped. Even if the defragmentation(Rebuild/Reorg) fails in the middle due to some reason, it will continue from the table it stopped.
Continue reading “Resumable Auto-Index Rebuild and Reorganize Script”
The Attached SQL Query can be safely used for rebuilding or reorganizing all indexes in a particular database based on the fragmentation level.
Note: The script is currently set to perform offline index rebuild since online index rebuild is supported only in Enterprise edition SQL Server. Inorder to change it into online mode, add
, ONLINE = ON statement along with
<span style="padding: 2px; border: 1px solid #00ff00;">FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON</span>
in line 100 in the attached script.
3203_AutoIndexRebuild2005andAbove (Click to download the script)
Continue reading “Automatically Rebuild or Reorg index based on fragmentation”