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?

BradC
  • 2,200
  • 4
  • 25
  • 35
  • 1
    Just in case anyone is in doubt - this appears to still be the case in 2012Sp1 :) –  Dec 19 '12 at 16:35

3 Answers3

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.

Paul Randal
  • 7,184
  • 1
  • 35
  • 45
  • 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 '09 at 19:16
  • 2
    Ah - very cool, but what terrible coding. I'll ping the right dev team and let them know. – Paul Randal Aug 25 '09 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...

BradC
  • 2,200
  • 4
  • 25
  • 35
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’
Amol
  • 1
  • 1