DONT KILL SPID

Killing a SQL Server thread? Don’t!

Sometimes, when you kill a session (i.e. a spid) in a SQL Server instance, the spid just refuses to go away not because it’s doing a rollback. Perhaps, it’s stuck on a certain dependency on something external to SQL Server or it’s just simply stuck for some decipherable reasons. And the spid may hang around for as long as the instance is online and will only go away when the instance is restarted.

In more than a few occasions, I have heard people wondering if they can just kill the thread of the SQL Server process from the OS, that is, the OS thread corresponding to the SQL Server spid.

That is a very bad idea! Killing such an OS thread will likely crash the SQL Server instance.

You can easily demonstrate the harm of doing this with Process Explorer. You’ll need to run Process Explorer locally on the server of your test SQL Server instance, and needless to say that this should not be done on a production instance.

First, connect to the test SQL Server instance, and open a New Query window in SQL Server Management Studio. Note the spid for the connection, and run the following query (replacing with the spid for the connection) to find the kpid value in the resultset for the spid:

select * from master..sysprocesses where spid =

This kpid is the id the OS assigns to the thread that is currently bound to the worker executing a task or idling on the spid.

Then, fire up Process Explorer and locate the OS process for the SQL Server instance. If you have multiple SQL Server instances running on the server, you can mouse over each sqlservr.exe to identify the right instance.

Double click on the process, and select the Thread tab. This will give you a list of all the OS threads for the SQL Server instances. Highlight the Thread ID corresponding to the kpid found above, and click on the Kill button to terminate the thread.

After you have killed the thread from Process Explorer, you may still be able to use the SQL Server instance for a while. You’ll for sure see stack dump error messages in the errorlog, and you may see error messages such as the following:

2010-02-04 14:45:31.03 Server Potential image corruption/hotpatch detected. This may be a sign of a hardware problem or caused by presence of CLR/jitted images on the stack. Check SQLDUMPER_ERRORLOG.log for details.

The session related to the kpid value would be dead. But the SQL Server instance itself may or may not continue to function. If you repeat the above steps to kill the threads for the other user sessions, eventually this will crash the SQL Server instance, i.e. the SQL Server instance will become unresponsive. After that, the only way to get the instance back online is to restart it. Most likely, you'll have to first kill the corresponding process from the OS because the instance probably won't respond to any of the normal restart methods.