Many times, I have to kill few blocking transactions or long running *badly written* queries in my environment so that all the important queries and processes complete in time.
When I kill any SPID then it shows in KILLED/ROLLBACK if I execute SP_WHO2 command .
Now if I want to know how long the ROLLBACK is going to take or how far it is complete then KILL command WITH STATUSONLY argument comes to my rescue.
According to the MSDN: ” WITH STATUSONLY generates a progress report about a specified session ID or UOW that is being rolled back due to an earlier KILL statement. KILL WITH STATUSONLY does not terminate or roll back the session ID or UOW; the command only displays the current progress of the rollback.”
So, for example I want to check the progress of the killed SPID (shown above), then I will use the following command:
KILL 56 WITH STATUSONLY