SQL Server resumable online index operations
Parent: Resumable index builds · Published reference · snapshot 2026-10-30
↓ Facts as markdownall context files
Source-anchored research for SQL Server resumable online index operations. 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.
How it works
- Resumable operations allow transaction-log truncation while work proceeds because they avoid one large transaction. Recovery state resides in database data files. This reduces log-space pressure but does not remove the need for adequate log throughput and log backups. (high confidence) [source]
How-to and procedures
- Use sys.index_resumable_operations to inspect state and percent_complete. Brent Ozar's reproducible 2020 demo shows that percentage can mislead when rows change during a pause; avoid treating it as a reliable ETA. (medium confidence) [source]
Measurements and reference values
- Resumable operations preserve progress across explicit pauses, failures, and failovers. In a dbi services SQL Server 2017 experiment, killing the SQL Server process left the rebuild paused at 19% after service restart; ALTER INDEX ... RESUME continued the operation. (high confidence) [source]
Problems, failure modes and limitations
- Microsoft's July 2026 guidance says paused operations reject transactions needing table-level exclusive locks, including INSERT with TABLOCK, with error 10637; remove the hint or complete the operation. (low confidence) (verified as of 2026-07-20) [source]
- SORT_IN_TEMPDB = ON is incompatible with resumable rebuilds; Niko Neugebauer reproduced error 11438 in the SQL Server 2017 preview, and Microsoft's September 2026 ALTER INDEX reference retains the restriction. (medium confidence) (verified as of 2026-09-21) [source]
- Use ALTER INDEX <index> ON <table> PAUSE, RESUME, or ABORT to control a resumable operation. Cancellation or failure can leave resumable work paused indefinitely; explicitly ABORT it to discard the operation. Aborted work cannot be resumed. (high confidence) [source]
Comparisons and alternatives
- The engine divides a rebuild into incremental units and persists continuation state, so interruption loses minimal progress instead of requiring a complete restart. (medium confidence) [source]
- Computed columns used as index keys can prevent resumable rebuilds, including implicitly inherited clustered-key columns in nonclustered indexes; inspect the effective key rather than only explicitly declared columns. (medium confidence) [source]
- Neugebauer's preview tests measured 65.8 versus 60.8 seconds on one table and 232 versus 230 seconds on a larger table; these tests excluded concurrent workload and do not establish production overhead. (low confidence) [source]
- The documented resumable rebuild restrictions exclude SORT_IN_TEMPDB = ON, explicit user transactions, ALTER INDEX REBUILD ALL, columnstore indexes, filtered indexes, and disabled indexes. A maintenance script must check eligibility rather than apply resumability to every index. (high confidence) (verified as of 2026-09-21) [source]
Facts and statements
- SQL Server introduced resumable online index rebuilds in 2017 and resumable online index creation in 2019. RESUMABLE = ON requires ONLINE = ON; an ordinary online operation is not automatically resumable. (high confidence) (verified as of 2026-07-20) [source]
- Start a rebuild with ALTER INDEX ix ON dbo.t REBUILD WITH (ONLINE = ON, RESUMABLE = ON); control it with ALTER INDEX ix ON dbo.t PAUSE, RESUME, or ABORT. (medium confidence) [source]
- MAX_DURATION limits execution time in minutes before pausing. Cancelling or killing a resumable operation leaves retained work; ABORT explicitly discards it. (medium confidence) [source]
- A paused rebuild retains both index structures, consumes disk space, and continues to maintain them during DML. Write-heavy workloads can therefore pay overhead while the build is paused; pausing is not equivalent to releasing all resources. (high confidence) [source]
- MAX_DURATION on a resumable operation limits execution time in minutes before it pauses, allowing maintenance across multiple windows. RESUME can change MAXDOP. MAX_DURATION inside WAIT_AT_LOW_PRIORITY instead controls lock waiting, so these settings have distinct purposes. (high confidence) [source]
- sys.index_resumable_operations exposes resumable operations and execution state. Redgate demonstrates monitoring percent_complete, state_desc, start_time, last_pause_time, and total_execution_time; dbi services uses the same view to inspect state after a server interruption. (high confidence) [source]
- Online operations still need short-lived shared or schema-modification locks at phase boundaries. Resuming also reacquires necessary locks. WAIT_AT_LOW_PRIORITY manages lock waiting; ONLINE and RESUMABLE do not guarantee freedom from blocking. (high confidence) [source]
- Paul White reproduced a severe log-exhaustion/resume slowdown on SQL Server 2022 CU16 Developer Edition: a build taking about 78 seconds uninterrupted took 3 hours 19 minutes with repeated log-full pauses. This is a specific reproduction, not a general resumable-build performance estimate. (low confidence) (verified as of 2025-01-04) [source]
Children
- No children recorded.