SQL SERVER – Not Able to Kill SPID with KILL WITH STATUSONLY

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)