# SQL Server resumable index operations: ELEVATE_RESUMABLE and resumable constraints > Source-anchored research for SQL Server resumable index operations: ELEVATE_RESUMABLE and resumable constraints. Run status: COMPLETED. Parent: [Resumable index builds](https://llms-explorer.com/tree/resumable-index-builds/) · 6 facets · 14 facts · page: https://llms-explorer.com/tree/sql-server-resumable-index-operations-elevate-resumable-and-resumable-constraints/ ## 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](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver17) ## 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](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver17) - 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](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver17) ## 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](https://learn.microsoft.com/en-us/sql/relational-databases/security/resumable-add-table-constraints?view=sql-server-ver17) ## 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](https://learn.microsoft.com/en-us/sql/relational-databases/security/resumable-add-table-constraints?view=sql-server-ver17) - 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](https://www.mssqltips.com/sqlservertip/7618/pause-and-resume-creation-of-table-constraints-in-sql-server/) ## Comparisons and alternatives - Resumable constraint creation can continue after maintenance-window pauses, failovers or system failures rather than restarting all work. (medium confidence) — [source](https://learn.microsoft.com/en-us/sql/relational-databases/security/resumable-add-table-constraints?view=sql-server-ver17) ## 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](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver17) - 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](https://learn.microsoft.com/en-us/sql/relational-databases/security/resumable-add-table-constraints?view=sql-server-ver17) - Manage resumable constraint creation with ALTER INDEX ALL ON PAUSE, RESUME or ABORT; inspect progress using sys.index_resumable_operations. (high confidence) — [source](https://learn.microsoft.com/en-us/sql/relational-databases/security/resumable-add-table-constraints?view=sql-server-ver17) - 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](https://aws.amazon.com/blogs/database/amazon-rds-for-sql-server-now-supports-sql-server-2022/) - Microsoft requires ALTER permission on the table and specifies no new permission for resumable ALTER TABLE ADD CONSTRAINT. (low confidence) — [source](https://learn.microsoft.com/en-us/sql/relational-databases/security/resumable-add-table-constraints?view=sql-server-ver17) - 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](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver17) - 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](https://learn.microsoft.com/en-us/sql/relational-databases/security/resumable-add-table-constraints?view=sql-server-ver17)