# SQL Server resumable online index operations > Source-anchored research for SQL Server resumable online index operations. Run status: COMPLETED. Parent: [Resumable index builds](https://llms-explorer.com/tree/resumable-index-builds/) · 6 facets · 18 facts · page: https://llms-explorer.com/tree/sql-server-resumable-online-index-operations/ ## 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver17) ## 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](https://www.brentozar.com/archive/2020/03/video-what-percent-complete-is-that-index-build/) ## 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver17) ## 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver17) - 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](https://learn.microsoft.com/en-gb/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver17) - Use ALTER INDEX ON 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](https://learn.microsoft.com/en-gb/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver17) ## 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](https://www.vldb.org/pvldb/vol10/p1742-antonopoulos.pdf) - 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](https://github.com/olahallengren/sql-server-maintenance-solution/issues/776) - 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](https://www.sqlservercentral.com/blogs/resumable-online-index-rebuild) - 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver17) ## 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver17) - 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](https://learn.microsoft.com/en-gb/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver17) - 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](https://learn.microsoft.com/en-gb/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver17) - 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver17) - 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](https://learn.microsoft.com/en-gb/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver17) - 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](https://learn.microsoft.com/en-gb/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver17) - 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver17) - 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](https://www.sql.kiwi/2025/01/slow-resumable/)