3

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.

0
6
  1. 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.
  2. 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.

5

kill 61 with statusonly. SQL tells me that,Status report cannot be obtained. Rollback operation for Process ID 61 is not in progress

It seems Kill With statusonly reports ,only when the session is being killed manually

From Paul Randal :(Emphasis mine)

KILL WITH STATUSONLY generates a report only if the session ID or UOW is currently being rolled back because of a previous KILL session ID|UOW statement.

So in your case,the operation was not killed..So you will not be able to track status

You can use Sys.dm_Exec_requests to get rollback estimation time

select percent_complete,estimated_completion_time,* from 
sys.dm_exec_requests 
where status like '%rollback%'

further on resuming part,you can resume If you are using Index Reorg not Rebuild

some more info on reorg vs rebuild (I read about this on MSDN,but not able to find now)

Reorganize works on page level, so if you happen to stop in the middle, it will roll back that particular page which is 8kb and not the whole thing.

So if you are not using Reorg,only option is to restart index rebuild and it starts from beginning again

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.