Have you ever encountered the situation where everything or at least parts of your system is stalling? I’m sure most of you have seen this and after some investigation you take a look in SQL Servers Activity Monitor or fire a sp_who2 or even run a few DMV:s to see what’s going on in the database. There you see that one process is the root cause, blocking other processes and doing, nothing, (useful anyway). The reason for this can be many things, you could have a very large transaction, you could have extensive locking and as a result everything seems to just hang. Since the system is stalling and users are complaining you decide as a last resort to just kill the process and move on with your life. You then proceed to Management Studio and run the KILL <spid> command. Now comes the next problem. Nothing happens…, everything is still hanging.
Now you wonder what is going on? Fortunately there are some things you can do: You can run KILL <spid> WITH STATUSONLY (same spid as above) to get a progress report of the rollback. For example: KILL 68 GO KILL 68 WITH STATUSONLY GO
Can result in this: spid 68: Transaction rollback in progress. Estimated rollback completion: 75% Estimated time left: 20 seconds. There is also the sys.dm_exec_requests dynamic management view that we can query. SELECT session_id, command, status, percent_complete FROM sys.dm_exec_requests WHERE session_id = 68 Or if you want to view all killed/rollbacked sessions: SELECT session_id, command, status, percent_complete FROM sys.dm_exec_requests WHERE command IN (‘killed/rollback’,’rollback’) I use these simple commands to put my mind at ease. I don’t pay so much attention to the percent_complete number, for me it’s more important to see that something actually is happening so that i can decide what to do next.
If you have any questions about this or something else regarding SQL Server feel free to contact our SQL Server consultants.