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)..
Best Answer
First, I would recommend that you use one of the free scripts/tools to manage index rebuilds. One of the best known is Ola Hallengren's DB maintenance suite. This lets you control how fragmented an index must be before you try to reorganize or rebuild it.
Second - if you still have issues where the process seems to hang, try creating two jobs. The first job would actually run the index rebuild process. The second job would ensure that the first job doesn't run longer than necessary.
Let's call the first job 'Index Maint Job'. Do not schedule this job.
The second job would be scheduled for when you want the index maintenance to be run, and would execute this script:
This launches the job, waits until it's been given whatever time you feel is sufficient to complete, checks if it's still running, and stops it if it is.
(I had originally planned to use
TRY ... CATCH
to catch the error you get if the job isn't running and you try to stop it; however, that's a SQL Server Agent error, whichTRY ... CATCH
can't handle. So, we check to see if the job's still running instead.)The key element to know here is the
sp_start_job
does not wait for the job to finish; it returns once the job is started. So, this job has control of itself again at that point, and can wait however long you like (current setting [WAITFOR DELAY '003:00:00'
] is three hours) to see if the job is still running, and stop it if it is.FYI: As other have noted, an index reorganization happens gradually; I have a large fragmented PK index to try to defrag, and was planning on implementing this with Ola's scripts to make sure the process didn't run too long.