Sometime or the other we do feel the need to forcefully kill a session in SQL Server. It’s not recommended and shouldn’t be run on production unless otherwise absolutely required. However, in case necessary here is how to kill a session in SQL Server.
First, execute the below query. Assume that this is the session which you want to kill anyhow.
-- Simulate a problamatic query SELECT * from sys.objects a WAITFOR Delay '00:01:00'
Considering real life scenario, we first need to find the session the query is running in order to kill it, to do this execute the below query.
-- find session id select session_id,sqltext.text from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle)sqltext
The query returns the session ids and the current query being executed under that session as shown in below snapshot
The session id 54 is what we are looking for. Let’s now kill the session 54 using below query.
It’s pretty simple however do remember not to execute this on production unless you know what you are doing.