Want to timeout or skip SQL Server index rebuild statements if taking too long

Question

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)..

Answer 1

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:

DECLARE @jobname nvarchar(128) = N'Index Maint Job';
DECLARE @proc_start_date datetime = GETDATE();

EXECUTE sp_start_job @job_name = @jobname;

WAITFOR DELAY '003:00:00';


IF (EXISTS (SELECT 1
              FROM sysjobactivity ja
                     INNER JOIN sysjobs j ON (ja.job_id = j.job_id)
             WHERE j.name = @jobname
               AND start_execution_date >= @proc_start_date
               AND stop_execution_date IS NULL
           )
   )
BEGIN
    Print N'Aborting job ''' + @jobname + N'''...';
    EXECUTE sp_stop_job @job_name = @jobname;
END
ELSE PRINT N'Job ''' + @jobname + N''' finished.';

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, which TRY ... 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.

Answer 2

Why rebuild all indexes on a table.. instead choose the index fragmentation threshold and only touch the indexes that have high fragmentation count with page count > 5K (for e.g.) ? Use Ola's index maintenance e.g. @TimeLimit and @LockTimeout parameters or Minion Reindex (both are free to use - I have use Ola's Index but not minion reindex solution).

See my answer for more details.