Question: Why I am not able to KILL SPID with command KILL (spid) WITH STATUSONLY?
Answer: Recently in performance tuning consultancy, I received a very interesting question. One of the Jr. DBA, tried to KILL the long running query using the following script.
KILL (spid) WITH STATUSONLY
However, after running the above script, he immediately told me that why this script does not do anything.
Well, here is the answer for you.
If you want to kill any session, you can run following script.
KILL (spid)
Now when you run above script, at that time if there is a transaction which needs to be rolled back, they will be started rolling back. Now sometimes the scripts take time to roll back transactions. While a transaction is already rolling back due to KILL (spid) command and if you want to know the status of the rollback situation, You can actually run the command KILL (spid) WITH STATUSONLY.
In reality the command KILL spid WITH STATUSONLY does not do anything at all most of the time. It will show the status when the transaction is rolling back.
Well, that’s it. Let me know if you find this blog post helpful and leave a comment.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
3 Comments. Leave new
Thanks for the information. It saved my day.
Some spid won’t kill in suspended state. How to kill it forcefully? after killing it it still in who2 list but in killed state. How to eliminate this.
I did a kill xxx with statusonly and was very impolitely told:
Msg 6120, Level 16, State 1, Line 55
Status report cannot be obtained. Rollback operation for Process ID 57 is not in progress.
What’s more doing an ‘exec sp_who2’ that spid does not show up. But, it does show up when querying sys.dm_exec_sessions.
The sad thing is I have to get these sessions disconnected so I can use a refreshed database that must use the same database name according to a vendor. Evidently there’s a ton of work involved to get their application to point to a different database.
If anyone has any insight as to what’s going on, I’d greatly appreciate some help!
Thank you!