Resumable Maintenance in SQL Server: Index Rebuilds, Index Creation, and ALTER TABLE ADD CONSTRAINT

Introduction>

Large tables make routine maintenance risky. Traditional online index builds and ALTER TABLE ADD CONSTRAINT operations run as a single long‑lived transaction. If an outage or fail‑over occurs near the end, SQL Server rolls back everything, wasting time, expanding the transaction log, and extending maintenance windows. Beginning with SQL Server 2017, Microsoft introduced resumable maintenance so work can pause safely and continue later without losing progress. This post focuses on resumable online index creation and rebuild (2017–2022) and resumable online ADD CONSTRAINT(2022).

1 · Why Resumability Matters

• Smaller log footprint — Each ~100 000‑row batch commits independently, allowing the log to truncate.
• Recovery from interruption — Pause or fail‑over preserves progress; resume continues from last batch.
• Controlled windows — MAX_DURATION pauses work automatically after the specified time.
• Reduced blocking — Only a brief schema‑modification lock at final switch‑over.

2 · Resumable Online Index Rebuilds
Version support – SQL Server 2017+ (row‑store); SQL Server 2022 adds non‑clustered columnstore.
Edition requirement – Enterprise Edition on‑prem; Azure SQL supports resumable rebuild.

2.1 Internal Flow

• Parse statement with ONLINE = ON and RESUMABLE = ON.
• Engine reads ~100 000 rows into an internal batch and commits.
• Repeat until all data is processed or paused, failed, or timed out.
• On RESUME, engine reads metadata and restarts at next batch.
• Final phase acquires a brief SCH‑M lock to swap in the new index.

2.2 DDL and Control

ALTER INDEX IX_OrderDate ON dbo.SalesFact
    REBUILD WITH (
        ONLINE       = ON,
        RESUMABLE    = ON,
        MAX_DURATION = 90,
        MAXDOP       = 4
    );
-- Pause after current batch
ALTER INDEX IX_OrderDate ON dbo.SalesFact PAUSE;
-- Resume with lower DOP
ALTER INDEX IX_OrderDate ON dbo.SalesFact
    RESUME WITH (MAXDOP = 2, MAX_DURATION = 60);
-- Abort and roll back if required
ALTER INDEX IX_OrderDate ON dbo.SalesFact ABORT;

2.3 Monitoring

SELECT name,
       state_desc,           -- RUNNING | PAUSED | ABORTED
       percent_complete,
       total_elapsed_time/1000 AS seconds
FROM sys.index_resumable_operations;

3 · Resumable Online Index Creation
Version support – SQL Server 2019+ (row‑store); SQL Server 2022 adds non‑clustered columnstore.
Edition requirement – Enterprise Edition on‑prem; Azure SQL supports resumable create.

CREATE INDEX IX_CustomerDate
    ON dbo.SalesFact(OrderDate)
    INCLUDE (CustomerName, Amount)
    WITH (
        ONLINE       = ON,
        RESUMABLE    = ON,
        MAX_DURATION = 60
    );

Pause, resume, abort, and monitoring use the same commands and DMV as rebuilds.

4 · Resumable ALTER TABLE ADD CONSTRAINT (PK, UNIQUE)
Version support – SQL Server 2022.
Edition requirement – Enterprise Edition on‑prem.

4.1 Syntax

ALTER TABLE dbo.SalesFact
    ADD CONSTRAINT PK_SalesFact_Id PRIMARY KEY CLUSTERED (Id)
    WITH (
        ONLINE       = ON,
        RESUMABLE    = ON,
        MAX_DURATION = 120
    );

4.2 Control Commands

-- Pause hidden index build
ALTER INDEX ALL ON dbo.SalesFact PAUSE;
-- Resume
ALTER INDEX ALL ON dbo.SalesFact
    RESUME WITH (MAX_DURATION = 60);
-- Abort
ALTER INDEX ALL ON dbo.SalesFact ABORT;

4.3 Monitoring

SELECT name,
       operation_type_desc,    -- ADD_CONSTRAINT
       state_desc,
       percent_complete
FROM sys.index_resumable_operations
WHERE operation_type_desc = 'ADD_CONSTRAINT';

5 · Database‑Scoped Enforcement

ALTER DATABASE SCOPED CONFIGURATION
    SET ELEVATE_RESUMABLE = WHEN_SUPPORTED;   -- or FAIL_UNSUPPORTED

• WHEN_SUPPORTED silently adds RESUMABLE = ON.
• FAIL_UNSUPPORTED errors on non‑resumable DDL.

6 · Best‑Practice Checklist

• Always set MAX_DURATION to define maintenance windows.
• Monitor DMVs and alert on unexpected PAUSED states.
• Adjust MAXDOP on resume to limit CPU impact.
• Ensure sufficient tempdb and log space for batch processing.
• Use ABORT only when discarding all progress is acceptable.
• Enable ELEVATE_RESUMABLE to enforce resumability across scripts.

Conclusion

Resumable online index rebuilds, resumable online index creation, and resumable ALTER TABLE ADD CONSTRAINT convert all‑or‑nothing maintenance into interruption‑tolerant processes. By batching work, persisting metadata, and exposing pause/resume controls, SQL Server minimizes log growth, shortens outage windows, and allows safe recovery after interruptions. Implement these features with proper DMV monitoring and database‑scoped enforcement to achieve reliable, low‑impact schema maintenance on large production systems.

Further reading:

• Guidelines for Online Index Operations
• Resumable add table constraints
• Perform Index Operations Online