What happens when you cancel or kill a resumable index creation?


SQL Server 2019 adds resumable online index creation, and it’s pretty spiffy:

Those parameters mean:

  • ONLINE = ON means you’ve got the money for Enterprise Edition
  • RESUMABLE = ON means you can pause the index creation and start it again later
  • MAX_DURATION = 1 means work for 1 minute, and then gracefully pause yourself to pick up again later

If you decide you want to pause the index, run this command in another session:

When you do that, the index creation’s session fails with a bit of an ugly error:

Aborted index creation

Okay, well, that’s not exactly what I’d expect from “pausing” something, but that’s okay. There’s a new DMV, sys.index_resumable_operations, that shows the paused index creation:


It shows the syntax used to create the index, the percentage complete, when it was last paused, and more. If you want to give it a happy ending or abort it, it’s your choice:

So far, so good.

The first catch: a paused index is stealthy.

It doesn’t show up in sys.indexes, nor does it show up in the Object Explorer list of indexes on a table. That makes it sound like it’s harmless, but it’s not. I can’t drop the table:

Resumable index operation error

Nor can I create any other index – same error:

Index creation error

Tricky. New stuff for developers to watch out for in their deployment scripts.

The next catch: killing or canceling an index creation, doesn’t.

In SSMS, you’re used to being able to click the “Cancel” button on your query, and having your work rolled back.

You’re also used to being able to kill a query, and have it automatically roll back.

Neither of those are true with resumable index creations. In both cases, whether you kill the index creation statement or just hit the Cancel button in SSMS to abort your request, your index creation statement is simply paused until you’re ready to come back to it. (Or, it’s ready to come back to haunt you, as we saw above.)

So you’ve learned your lesson, right? Don’t use the resumable parameter if you don’t specifically need it. As long as you don’t ask for it, you should be fine…right? Wrong.

The final gotcha: you might get resumable even when you don’t ask for it.

SQL Server 2019 adds these database-level configuration options:

This means whenever you create an index, it’ll default to online creation and resumable even when you don’t specify it as part of your index creation statement! Translation: whenever you go to create any index, if you decide it’s a bad idea and click Cancel, your index creation is still left online partway done, lying in wait to disrupt other operations.

That’s why I’ve added an sp_Blitz check for resumable index operations that have been left behind. It’s in the dev branch now, and it’ll be in the April release. Enjoy!

Previous Post
Updated First Responder Kit and Consultant Toolkit for March 2019
Next Post
What Are You Going to Do About SQL Server 2008?

22 Comments. Leave new

  • Wow. That is somewhat ugly. Like a landmine.
    Great that you are adding it to your tools for visibility.

  • Can you add data to table with an index build waiting to resume? It would seem that if you were allowed to a resume would have to basically start over from the beginning.

    This could also be one of those features that started out like columnstore indexes in 2012 where they had way less features then they needed to be truly useful but they got added over time

  • Ross Presser
    March 25, 2019 2:08 pm

    Can you drop a paused resumable index? Or are your only choices to either resume it or leave it there?

  • Nice post Brent. And being somewhat anal, have to say I don’t like the syntax. ALTER INDEX Blah PAUSE sounds like it should pause the index, which it doesn’t. Why oh why do we keep having new syntax added that doesn’t do what it says? Why not PAUSE_REBUILD or something? I must be getting old.

  • Alex Friedman
    March 26, 2019 2:17 am

    Ooooh I love ELEVATE_ONLINE. Very handy for those oops-forgot-to-add-ONLINE-ON-and-blocked-production-forever cases.

    • Brian Boodman
      March 26, 2019 6:39 am

      All ELEVATE_ONLINE does is default all index builds to online. The problem is that ONLINE indexes take way, way longer to build, with a nasty performance hit while it is running.

  • Robert Barnard
    March 26, 2019 7:07 am

    Great information here. Thank you all.

  • I just checked and both of the ELEVATE configuration options default to OFF. That’s a good thing. My foot feels less worried about me accidentally shooting it now.

  • I just had an interesting issue with a partitioned clustered index that i was rebuilding just a partition of.
    Partitioned on DATE (varchar) field in YYYYMMDD format.

    we have yearly partitions in place : 17 is for data for 2016, 18 for 2017 etc…

    The VM has 6 CPUs and 320GB RAM, and Nimble SAN storage in the background.

    I had the usual workload that can handle 1800 transactions per second drop to 50-100 per second.
    (the transactions are all INSERTs into the partitioned table-> into partition 22, for 2021)
    I left it running for 50 minutes (it had managed 25% of the clustered index on partition 17 (for 2016)), and the backlog was getting too high so i decided to PAUSE it with “ALTER INDEX [IDX_FK_MyIndex] ON [dbo].[T_PARTITIONED_TABLE] PAUSE”

    Even with this paused, i only got up to 150/200 transactions/second.
    After more fretting and sweating, i used the ABORT command to see if this or some ‘hidden’ workload was causing the slowness. And bam, after the abort it jumped right back up to 1800 trans/sec.

    Not a request for support ;), but was curious – I thought partitioned indexes were independent, but the pause of an online resumable rebuild on an otherwise inactive partition affects the entire index, spanning across all partitions ?
    Has anyone seen anything like this, or have an explanation? I couldn’t find anything on MS online docs saying anything about this…

  • Good lord! As if there aren’t enough real issues…

    Thanks for taking the time to flush these faults out and then post them, Brent! You ARE the MAN!

  • Tim Cartwright
    September 2, 2021 6:09 pm

    Wow, once again this one of those things you read about in SQL Server, and think AWESOME!!! What we needed! Then the hammer drops. Thanks for the informative post as always Brent.


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.