Home / server / Questions / 58583
Accepted
BradC
Asked: 2009-08-26 09:56:15 +0800 CST

Can't see progress of rolling back SPID with KILL WITH STATUSONLY

  • 772

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?

sql-server rollback

3 Answers

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

    • 5
  2. Best Answer

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

    • 3

  3. 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’
    • 0