Resume a suspended task

I was applying an index to a database with archived data. I knew this would take quite a while. It was at 14 hours running when I left it last night. I came back to it this morning and enterprise manager told me it had lost connection.

On checking activity monitor on the server, I can see the task. Its state is SUSPENDED with a wait type of IO_COMPLETION.

Is there a way to resume this or kill it off and start again?

I ran sp_who2 but it's not being blocked by any other activity and has a status of ROLLBACK. If I then run kill 61 with statusonly, SQL tells me that;


Status report cannot be obtained. Rollback operation for Process ID 61
is not in progress.

Just looking for options on what I can do/try next. Thanks.

• If you kill it, you'll have to roll your progress so far back, which

could take a very long time. Rollbacks are single threaded. You
could be waiting much more than 14 hours for that to happen.

• You can't resume a suspended task. Suspended is one part of a task

life cycle in SQL Server. It means the task is running, but waiting
on a resource. In your case, that resource is IO_COMPLETION.
You're waiting for data to be written to disk.

Other parts of the task life cycle:

Running: Exactly what it sounds like. Your task is in flight.

Runnable: Your task is waiting to get on a CPU.

Sleeping: The command finished and is waiting for another command

Pending: Your task it waiting for a thread

There are also Background tasks, but you generally don't have to worry about those.

A short anecdote from a previous job: one of the developers decided to roll out an index on a 2 TB table in a 9 TB database. It ran all weekend and wasn't done before a data processing job Monday morning. We killed it, but it would have taken so long to roll back that it was faster to restore a copy of the database to work on.

StackExchange Database Administrators Q#167970, answer score: 7