Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
What is Resumable Index?
When rebuilding indexes online, index rebuild process could be fail for reasons such as failover, disk failure, manual stop (PAUSE). In such cases, you must mark the index as RESUMABLE = ON in order for the rebuild process to resume after it fails.
Create a Resumable Index
You can create a resumable index with the following script.
1 | CREATE NONCLUSTERED INDEX IndexName ON schemaname.tablename(Indexcolum) WITH (Online = ON,RESUMABLE = ON); |
Alter an Index With Resumable
You can alter the index as resumable with the following script.
1 | ALTER INDEX IndexName ON schemaname.tablename REBUILD WITH (ONLINE=ON,RESUMABLE=ON); |
If you do not add “ONLINE = ON” to the script, you will receive an error as follows.
Msg 11438, Level 15, State 1, Line 3
The RESUMABLE option cannot be set to ‘ON’ when the ONLINE option is set to ‘OFF’.
Pause Index Rebuild Process
Below is the code that pauses the index rebuild process.
1 | ALTER INDEX IndexName ON schemaname.tablename PAUSE; |
Resume Paused or Failed Index Rebuild Process
Below is the query that you can resume a paused Index Rebuild process.
Suppose you forgot to start Index rebuild with maxdop. You can increase the parallelism level of the query by pausing the Index Rebuild operation and changing the maxdop level while resume.
1 | ALTER INDEX IndexName ON schemaname.tablename RESUME WITH (MAXDOP=4) |
Resumable Index Options
In the following query, we resume the Index with different options. For other options in the query, I recommend reading “How To Manage Online Index Rebuild and Partition Switch Processes With WAIT_AT_LOW_PRIORITY“.
1 2 | ALTER INDEX IndexName ON schemaname.tablename RESUME WITH (MAXDOP=2, MAX_DURATION= 240 MINUTES, WAIT_AT_LOW_PRIORITY (MAX_DURATION=10, ABORT_AFTER_WAIT=BLOCKERS)) ; |
Abort Index Rebuild Process
We can abort the index rebuild process which is pause or running, with the help of the following query.
1 | ALTER INDEX IndexName ON schemaname.tablename ABORT; |
Resumable Index Limitations
- SORT_IN_TEMPDB = ON is not supported in RESUMABLE indexes.
- We cannot rebuild disabled indexes as RESUMABLE.
