How to kill a thread in SQL Server

In my last three blogs I talked about how to kill a session in SQL Server, how to kill multiple sessions in SQL server and how to kill all sessions in SQL Server. In this blog I will talk about how to kill a threads in SQL Server.

The blog doesn’t promote this as best practice and would recommend not running it on production server until necessary.

Every session in SQL Server has one or more thread associated with it. This can be observed by running the below query.

SELECT threads.thread_address,threads.worker_address,tasks.session_id
 FROM sys.dm_os_threads threads
INNER JOIN sys.dm_os_tasks tasks
ON tasks.worker_address = threads.worker_address
WHERE tasks.session_id = @@SPID

The above query returns thread details associated with a session. The sys.dm_os_tasks dmv lists the tasks in SQL Server. A task in SQL server represents the work need to be performed. The sys.dm_os_tasks contains the worker_Address which is the memory address of the thread running the task. The sys.dm_os_threads dmv lists available threads and joining these two dmvs on worker_address column gives us the session id the tasks and thread belong too as shown in below snapshot.

   

how to kill a thread in sql server

Thus, killing the session 57 will kill the associated threads.

KILL 57

 
Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

   

Leave a Reply

Your email address will not be published.