4

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

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.

  • Thank you, @RDFozz. Forcing a job to stop is the best solution. I will try to adapt this so that the Index Maint Job deals with one statement (Top 1) and removes it from the list. It will be started within a loop until the list is complete, stopping the job if still running after 30 mins, otherwise moving on to the next. – cloudsafe May 4 '17 at 9:22
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.

  • Thank you, @Kin. That(Ola's) will assist me greatly. However, I have recently run ALTER INDEX statements against individual indexes and not tables, resulting in the same stop, which I have experienced lasting all weekend. I think the scripts you recommended will end up with the same results. The timeouts are for not running any new statements, not rolling back existing ones. – cloudsafe May 3 '17 at 16:25
  • 3
    @cloudsafe The only way you could get the maintenance to stop mid-statement and roll back, you would need to schedule a second job to do an sp_stop_job to kill your maintenance job. – AMtwo May 3 '17 at 16:40
  • 1
    @cloudsafe AMTwo has answered your concern. Rebuid prior to SQL Server 2017 is all or nothing vs reorg which restarts from the point it got killed. Refer to my linked answer so you have some more ideas on how to deal with large indexes. Also, is index fragmentation causing you any issues ? – Kin Shah May 3 '17 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.