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