Infrastructure at your Service

Stéphane Haby

SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?

Last week, I presented this option in our internal event, the dbi xChange.

My colleague David Barbarin asked me this question, what’s append if an interruption occurs (reboot, SQL Server service stopped,…) during a Resumable Online Index Rebuild?

I was not able to answer because I forgot to do this test.

The new enhancement for an index rebuild provides the capability to suspend, resume or abort an online index operation (msdn reference). I always use the option PAUSE to see how it works but never with a break.

Now I do the test to have the answer. It’s never too late! ;-)

To do my demo, I downloaded the Microsoft example database WideWorldImportersDW from github.

I choose the index NCI_dbo_Transaction in the Transaction table.

riro_00

As you can see with this query, the index is big enough to have the time to interrupt the rebuild.

I run the rebuild with this command:

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

After killing the SQL Server task in the task manager, I have this message in SSMS:
riro_01
Of course, the server is no more available…

I start the SQL Server service and have a look on the index state with the DMV sys.index_resumable_operations:
riro_02
As you can see the index is on state PAUSE with 19% completed.

To restart, I run the rebuild with the RESUME option:
riro_03

If the index rebuild is interrupted, you don’t need to restart from scratch.
The rebuild is in a PAUSE state and then you can decide or not to continue the rebuild.
Very clever option, isn’t it?

Don’t hesitate to go on David’s blog to read how this option works with AlwaysOn:
https://blog.dbi-services.com/sql-server-2017-high-availability-and-resumable-online-indexes/

Leave a Reply

Stéphane Haby
Stéphane Haby

Delivery Manager