Online and resumable DDL operations
The online CREATE INDEX and REBUILD INDEX operations can be paused and resumed as and when required, or when killed/failed.
The operation is marked as resumable by specifying RESUMABLE=ON. For example, the following CREATE INDEX operation is a resumable operation:
CREATE INDEX IX_Orders_CustomerID_Includes ON Sales.Orders(CustomerID,Comments) INCLUDE(DeliveryInstructions,InternalComments) WITH(ONLINE=ON,MAXDOP=1,RESUMABLE=ON) GO
To pause an ongoing online resumable CREATE INDEX operation, either kill the session or execute the PAUSE statement, as shown here:
ALTER INDEX IX_Orders_CustomerID_Includes on Sales.Orders PAUSE GO
To resume a paused online resumable CREATE INDEX operation, either execute the CREATE INDEX query mentioned earlier or execute the following query:
ALTER INDEX IX_Orders_CustomerID_Includes on Sales.Orders RESUME GO
You can also specify MAX_DURATION in minutes that the resumable operation should run before...