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 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.