When running index rebuild scripts overnight on large and highly fragmented databases we frequently come across indexes that stop the job because they seemingly never complete their rebuild. The job is halted and in the morning we have to manually cancel the statement, waiting forever for the rollback, causing downtime. We can remedy these occasional indexes my manually dropping and recreating.
Is there a way to automatically timeout an index rebuild statement if there is little progress, so the script can carry on to the next rebuild statement?
Our script is of the form:
ALTER INDEX ALL ON [Table1] REBUILD WITH (FILLFACTOR = 90) ALTER INDEX ALL ON [Table2] REBUILD WITH (FILLFACTOR = 90) ALTER INDEX ALL ON [Table3] REBUILD WITH (FILLFACTOR = 90)..