Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Just in case anyone is in doubt - this appears to still be the case in 2012Sp1 :) –  user150277 Dec 19 '12 at 16:35

2 Answers 2

up vote 3 down vote accepted

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...

share|improve this answer

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.

share|improve this answer
    
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
1  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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