Resumable Index Rebuilds: Pausing Maintenance Without Losing Progress

A large index rebuild can outlast the maintenance window, and canceling it throws away its progress. A resumable index rebuild lets you pause at the window boundary and continue later.

Check Resumable Index Support and Space First

A pausable rebuild requires ONLINE = ON and a supported SQL Server edition and index form. Verify support on the installed version before scheduling a large operation. I check free data and log space, availability replicas, and the backup schedule. A resumable operation can hold log and space obligations while paused. Pausing does not rewind its effects on storage. The table and index remain usable, but DDL on the affected object can be constrained until the operation completes or is aborted.

Measure a rehearsal on a restored copy. A rebuild that takes several windows needs a retention and monitoring plan between those windows, not just a command that says RESUMABLE.

Start With a Time Boundary

ALTER INDEX REBUILD supports ONLINE = ON, RESUMABLE = ON, and MAX_DURATION in minutes where supported. MAX_DURATION pauses the operation after the specified period. Use the exact index and table names from your maintenance inventory. I keep the command in a job that records its start and outcome. Do not launch every index at once just because each one can pause.

ALTER INDEX IX_OrderDate ON dbo.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

Read the Resumable Index Operation State

sys.index_resumable_operations reports active or paused work, progress percentage, start time, and other status details. Query it after the time boundary and before the next window. An operation stuck paused still needs an owner. I compare progress with actual log and disk use, since percent_complete alone does not tell the full resource cost. If a job says "success" because it paused as designed, the runbook must still mark the maintenance task unfinished.

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS table_name, index_id,
       state_desc, percent_complete, start_time, last_pause_time
FROM sys.index_resumable_operations
ORDER BY start_time;

Pause, Resume, or Abort Deliberately

ALTER INDEX … PAUSE stops an active resumable operation, so it runs from a second session while the rebuild is still working. The rebuild session is then disconnected with error 1219, which is the expected sign of a pause. ALTER INDEX … RESUME continues the work in a later window. PAUSE with nothing running fails with error 10638, so the script below checks the DMV first. ABORT discards the in-progress rebuild and releases its obligations. I use ABORT only after deciding to abandon the work, because it loses progress. Before resuming, check data and log free space and current workload. A window that has shrunk since last week can make the same MAX_DURATION unsafe.

-- From a second session while the rebuild runs
IF EXISTS (SELECT 1 FROM sys.index_resumable_operations
           WHERE object_id = OBJECT_ID(N'dbo.Orders') AND state_desc = N'RUNNING')
    ALTER INDEX IX_OrderDate ON dbo.Orders PAUSE;

Run the resume step as its own batch in the next window. If both steps run in one batch, the second check finds the fresh pause and resumes the rebuild at once.

-- In the next maintenance window
IF EXISTS (SELECT 1 FROM sys.index_resumable_operations
           WHERE object_id = OBJECT_ID(N'dbo.Orders') AND state_desc = N'PAUSED')
    ALTER INDEX IX_OrderDate ON dbo.Orders RESUME
    WITH (MAX_DURATION = 60 MINUTES);

Understand the Paused Cost

A paused resumable rebuild can retain allocated space and delay log truncation. Monitor log_reuse_wait_desc, file growth, and availability replica health. I include paused operations in the daily operational dashboard so they are not forgotten between maintenance windows. The setting is not a free checkpoint. It trades a shorter continuous window for ongoing resource management. If the index changes heavily while paused, the total work and completion time deserve another look.

Ask whether the index truly needs a rebuild. A statistics update or targeted index change can solve the report issue without rebuilding a huge structure. Resumability is useful when rebuild work is justified.

Review the Database Default

ELEVATE_RESUMABLE is a database scoped configuration that can make eligible rebuilds resumable by default under its supported modes. Check its current value before changing scripts, since it can affect maintenance commands that omit RESUMABLE. I prefer explicit behavior in a controlled job and a documented database default. Test both the command and the monitoring response after an upgrade or policy change.

SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = N'ELEVATE_RESUMABLE';

Know What a Pause Leaves Behind

The paused operation retains state so it can resume. That state consumes space and can affect transaction log reuse. Check sys.databases.log_reuse_wait_desc, log file usage, and available data space before starting the next maintenance window. I add an alert for operations paused longer than the agreed period. A rebuild paused for weeks is not harmless storage decoration. It can complicate schema changes and backup capacity planning.

Do not confuse PAUSE with canceling an ordinary rebuild. An ordinary canceled rebuild rolls its work back. A resumable rebuild's pause is an intentional checkpoint with continuing obligations. Record the owner, next resume window, and abort decision. If the index is no longer needed, ABORT the operation under a reviewed change and reclaim its resources.

Test Before Making Resumable Index Work the Default

The database-scoped ELEVATE_RESUMABLE setting can affect other eligible rebuilds beyond the one you are testing. Review edition support, index type, ONLINE requirements, and maintenance scripts before changing it. I prefer a narrow explicit RESUMABLE = ON command for a first rollout. After the team can monitor and resume operations, a database default can be considered. An unsupported operation should fail or fall back according to the configured mode; know which behavior your job will see.

What counts as completion in your dashboard? When MAX_DURATION pauses the operation, the job status should read "paused, resume scheduled" and never "index rebuilt" at that point. I query the resumable operations DMV after each job run and again before business hours. The final validation includes index health, no lingering resumable row, normal log reuse, and measured application behavior. That is the full lifecycle the feature was built to support.

A paused operation needs a named owner in the change calendar. I include the index, table, state, percent complete, log usage, and next resume window in the handoff. If the next shift cannot tell whether the rebuild should resume or abort, the feature has merely moved uncertainty from one night to another. Completion is a separate verified event.

Verify Completion and Release

After RESUME finishes, confirm the operation disappears from sys.index_resumable_operations, the index is online and usable, and log reuse returns to normal. Check the job history and application workload. I keep the start, pause, resume, and final completion times together. A rebuild that paused successfully for three nights but never completed is not successful maintenance.

Use the same review for the last index as the first. The large object at the end of a window is the one most likely to be left in a paused state when everyone thinks the job is done.

Related reading on this blog: Ins and Outs of Online Index Operations and Your Index Rebuild Maintenance Plan Is Rebuilding Indexes Nobody Uses.

A paused rebuild is not finished maintenance, it is unfinished work still using resources.

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