Check progress of rollback

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

In SQL server, you can klii an active process using the command KILL <SPID> causing it to roll back any in flight transactions. And if you have killed a process, you can check the progress of the rollback by running KILL <SPID> WITH STATUSONLY. But sometimes, for various reasons (example), SQL Server does not return any status information even if sp_who2 and Activity Monitor shows that the SPID status is KILLED/ROLLBACK.

What I find tends to work every time however, it the following little query that you can run instead:

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

In addition to the rollback percent complete, it gives you some info about if and what the process is waiting for, in case you feel it is taking a long time, and you want to investigate that.

As usual, feel free to contact us if you have SQL server questions, needs or problems!