SQL SERVER – How to Abort Index Rebuilding After Specific Time?

An index rebuilding limit can control lock waiting or a resumable execution interval. I decide which behavior the maintenance window needs.

A workshop attendee wanted to stop rebuilding after ten minutes and retry when the table was quieter. I’d used the low-priority wait option at three customer sites. Its waiting limit still isn’t a total runtime limit.

-- Administrator examples only; verify edition and index eligibility.
-- ALTER INDEX [AK_Employee_LoginID] ON [HumanResources].[Employee]
-- REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY
--     (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = SELF)));

-- SQL Server 2017+ resumable online rebuild: pause after an execution budget.
-- ALTER INDEX [AK_Employee_LoginID] ON [HumanResources].[Employee]
-- REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 10 MINUTES);
-- Inspect paused operations in the relevant database:
SELECT name, state_desc, percent_complete, start_time, last_pause_time
FROM sys.index_resumable_operations;

WAIT_AT_LOW_PRIORITY, available for eligible online rebuilds from SQL Server 2014, limits the low-priority wait for required locks. ABORT_AFTER_WAIT = SELF ends the index operation if that wait expires. It does not cap the total rebuild runtime at ten minutes. Lock acquisition is needed at stages of an online operation, including completion, so the limit is not only about starting.

Supported resumable online rebuilds arrived in SQL Server 2017. Their outer MAX_DURATION limits an execution interval and pauses unfinished work. A paused rebuild stays recorded and consumes resources. It isn’t completed or rolled back until the appropriate later action.

The original BLOCKERS alternative terminates blocking user transactions and needs additional permission. It should not be a casual replacement for SELF. Confirm edition support, eligible index types and the intended maintenance behavior before running either template.

Reference: Online lock waits and resumable rebuilds.

Related reading

• SQL Server Performance Tuning Practical Workshop

A paused rebuild is not a completed rebuild, it is an unfinished operation that still needs management.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.