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.
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
