Azure SQL Database / SQL Server – Paused Index Rebuild

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

itspaused

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

resumedindex.JPG

It moves into a RUNNING state and completes soon after.

 

 

One thought on “Azure SQL Database / SQL Server – Paused Index Rebuild

  1. Pingback: Don’t Forget Those Paused Indexes – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s