Infrastructure at your Service

Stéphane Haby

SQL Server 2017: Can I run a “standard” rebuild on the index after a Resumable Online Index Rebuild is suspended?

This article is an additional article to my precedent about “SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?

The goal is to see after I suspend the rebuild, if I can run a traditional rebuild without the option RESUME.

 

I start an index rebuild with the option Resumable:

ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction]
REBUILD WITH (RESUMABLE = ON, ONLINE = ON)
GO

 

In a new query window, I stopped the rebuild:

USE [WideWorldImportersDW]
GO
ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] 
PAUSE
GO

 

As you can see, the index rebuild is stopped:
riro2_00

 

I have a look on the index state with the DMV sys.index_resumable_operations:
riro2_01

 

To restart, I run the rebuild without the RESUME or ABORT options:
riro2_02

As you can see, the query does not work and you have this error message:
Msg 10637, Level 16, State 1, Line 14
Cannot perform this operation on ‘object’ with ID 430624577 as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

The standard rebuild will not change the status of the resumable rebuild and begin from scratch in order to do the rebuild from beginning.
Finally, the only way to rebuild the index is really to use the RESUME option and continue where it’s stopped.

Leave a Reply

Stéphane Haby
Stéphane Haby

Delivery Manager