2

I have a SPID on SQL 2005 that shows in Activity Monitor as "ROLLBACK" mode (because a transaction log filled up, not because it was manually killed). I tried to see how much time it has left to roll back with a

KILL 115 WITH STATUSONLY

but it just said "Status report cannot be obtained. Rollback operation for Process ID 115 is not in progress."

Can I safely issue a "KILL 115" so that I can see the rollback status? Does this actually do anything on a spid currently in rollback?

1
  • 1
    Just in case anyone is in doubt - this appears to still be the case in 2012Sp1 :)
    – user150277
    Dec 19, 2012 at 16:35

3 Answers 3

5

Nope it doesn't do anything. You cannot kill a spid that's rolling back. The rollback has to complete otherwise the database is transactionally inconsistent and becomes SUSPECT.

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

In this case, it rolled back on its own so you won't be able to see progress.

Hope this helps.

2
  • But curiously enough, it does do something. I tried it, and it doesn't kill the rollback of course, but once you run the "KILL 155", it appears to flag it differently internally, so you can run the KILL WITH STATUSONLY, and actually get a progress report.
    – BradC
    Aug 25, 2009 at 19:16
  • 2
    Ah - very cool, but what terrible coding. I'll ping the right dev team and let them know. Aug 25, 2009 at 19:53
3

So it looks like you can kill a SPID that is currently in rollback. While I still don't have any insight into the internals of what's happening, it appears that once you execute a KILL you can then get the progress by using KILL WITH STATUSONLY.

As Paul indicates, this is pretty arbitrary...

0

Try using the below statement to see the progress of the rollback process.

select session_id, percent_complete , last_wait_type , wait_resource, wait_time, wait_type  from sys.dm_exec_requests where status = ‘rollback’

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .