How to kill a session in SQL Server

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

how to kill a session in SQL Server

The session id 54 is what we are looking for. Let’s now kill the session 54 using below query.

KILL 54

It’s pretty simple however do remember not to execute this on production unless you know what you are doing.

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

Leave a Reply

Your email address will not be published. Required fields are marked *