SQL Server resumable index operations: ELEVATE_RESUMABLE and resumable constraints
Parent: Resumable index builds · Published reference · snapshot 2026-10-30
↓ Facts as markdownall context files
Source-anchored research for SQL Server resumable index operations: ELEVATE_RESUMABLE and resumable constraints. Run status: COMPLETED.
These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.
Definitions
- ELEVATE_RESUMABLE is a database-scoped default available from SQL Server 2019: OFF is the default, WHEN_SUPPORTED elevates eligible operations and lets unsupported ones run nonresumably, and FAIL_UNSUPPORTED rejects unsupported execution within its scope. Configure it with ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED. (high confidence) (verified as of 2026-07-20) [source]
How it works
- ELEVATE_RESUMABLE affects only DDL supporting RESUMABLE syntax when the statement omits that option. Explicit RESUMABLE = OFF overrides even FAIL_UNSUPPORTED; XML index operations are outside the setting. Treat this as a default, not an unconditional enforcement policy. (high confidence) [source]
- Database defaults can make eligible index DDL resumable without an explicit RESUMABLE clause. Brent Ozar demonstrates why callers must account for retained paused work when cancelling such DDL. (medium confidence) [source]
How-to and procedures
- Enable resumable constraint creation with WITH (ONLINE = ON, RESUMABLE = ON). MAX_DURATION specifies minutes until automatic pause; if omitted, execution continues to completion. (high confidence) [source]
Problems, failure modes and limitations
- Microsoft's constraint documentation shows manual PAUSE disconnecting the original session with error 1219, followed by error 1750, while the operation remains PAUSED in sys.index_resumable_operations. (low confidence) [source]
- A failed constraint build can remain paused. MSSQLTips demonstrates a duplicate-key failure followed by error 10637 on attempted constraint removal; inspect sys.index_resumable_operations and explicitly ABORT work that should be discarded. (medium confidence) [source]
Comparisons and alternatives
- Resumable constraint creation can continue after maintenance-window pauses, failovers or system failures rather than restarting all work. (medium confidence) [source]
Facts and statements
- ELEVATE_RESUMABLE is documented for SQL Server 2019 and later, Azure SQL Database and Azure SQL Managed Instance; it supplies a database-scoped resumable default for eligible DDL. (medium confidence) (verified as of 2026-10-06) [source]
- SQL Server 2022 adds resumable ALTER TABLE ADD CONSTRAINT for PRIMARY KEY and UNIQUE constraints, excluding FOREIGN KEY constraints; execution requires ONLINE = ON. (high confidence) (verified as of 2025-11-18) [source]
- Manage resumable constraint creation with ALTER INDEX ALL ON <table> PAUSE, RESUME or ABORT; inspect progress using sys.index_resumable_operations. (high confidence) [source]
- AWS announced that Amazon RDS for SQL Server 2022 supports pausing and resuming ALTER TABLE ADD CONSTRAINT, including recovery after failovers or system failures. (low confidence) (verified as of 2023-11-16) [source]
- Microsoft requires ALTER permission on the table and specifies no new permission for resumable ALTER TABLE ADD CONSTRAINT. (low confidence) [source]
- The database-scoped configuration reference documents PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES with a 1,440-minute default and zero disabling automatic abort; verify platform support and actual configuration before assuming indefinite retention. (low confidence) (verified as of 2026-10-06) [source]
- Monitor constraint creation through sys.index_resumable_operations using sql_text, state_desc and percent_complete. The catalog documents total_execution_time in minutes and state values 0 for running and 1 for paused. (high confidence) [source]
Children
- No children recorded.