I was creating some demo non-clustered indexes in one of my Azure SQL Databases and received the following warning when I executed this code:

CREATE NONCLUSTERED INDEX [dbo.NCI_Time]
ON [dbo].[Audit] ([UserId])
INCLUDE ([DefID],[ShopID])

Msg 10637, Level 16, State 3, Line 7

Cannot perform this operation on ‘object’ with ID 1093578934 as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

How intriguing! So I queried sys.index_resumable_operations.

SELECT * FROM sys.index_resumable_operations

I totally forgot about my paused index rebuild! Well I can’t create a non-clustered index on this table if my Clustered index rebuild is paused right? You have 2 options here, either you resume the rebuild or you abort it. This feature also applies to SQL Server – starting with SQL Server 2017 (14.x) along with the cloud variant.

I decided to abort the clustered index rebuild and then issue my non clustered index creation script.

ALTER INDEX [PK_Audit] on [dbo].[Audit] ABORT

I finally see the much wanted – Command(s) completed successfully message.

Most likely you will probably want to RESUME the index rebuild which you can do via the following command.

ALTER INDEX [PK_ACT_Activity] ON [dbo].[ACT_Activity] RESUME

It moves into a RUNNING state and completes soon after.