When Pause and Resume came out for indexes, I didn’t have an instance that supported them, and then I completely forgot about this feature. Recently, I had a very large table that I needed to build an index on and I figured it was a great time to try it.

I started to build my index and had set RESUMABLE = ON.

CREATE INDEX [IX_SuperHero_BirthDate_PowerKey] ON [dbo].[SuperHero]
([BirthDate], [PowerKey])
INCLUDE ([FirstSeenKey]) WITH (ONLINE = ON, RESUMABLE = ON)

It had been building for about 10 minutes, when I realized I had the fields in the wrong order. No big deal, I just canceled it, corrected it and tried to start it again. “Tried” was the keyword here. I got an error because I had set RESUMABLE = ON but hadn’t properly cleared it out of the system. I started to furiously dig through articles about how to clear out an index that was resumable but had been canceled.

Thankfully I found this fantastic article from Brent Ozar which saved me. It told me that I had to “Abort” my index before I could start over.

ALTER INDEX [IX_SuperHero_BirthDate_PowerKey] ON [dbo].[SuperHero] ABORT

It ended my process and I was able to restart the create on my index. It took a long time but successfully created and took a 25 minute query down to a few seconds.

The song for this post is Unconditional by Matt Maeson.