Sql Server: Find and Kill Specific User Session


Recently I was asked for query to find out currently active user sessions for a specific database and kill specific session.
Following is the t_sql I usually use to find a specific session (which is creating problem and need to be killed immediately)
SELECT DISTINCT
        name AS database_name,
        session_id,
        host_name,
        login_time,
        login_name,
        reads,
        writes
FROM    sys.dm_exec_sessions
        LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
        INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE   resource_type ‘DATABASE’
–AND name =’YourDatabaseNameHere’
ORDER BY name
This query shows all active user sessions.  Once we got process id of required user process, we can it with following simple statement
KILL 86 — Kill process having session_id 86
To find out sessions which acquired EXCLUSIVE locks, modify above mentioned query as follow
SELECT DISTINCT
        name AS database_name,
        session_id,
        host_name,
        login_time,
        login_name,
        reads,
        writes
FROM    sys.dm_exec_sessions
        LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
        INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE   resource_type ‘DATABASE’
AND request_mode LIKE ‘%X%’
–AND name =’YourDatabaseNameHere’
ORDER BY name
Advertisements

Posted on December 24, 2010, in Sql Server Management, TSQL Tips n Tricks. Bookmark the permalink. 2 Comments.

  1. Thank you so much…It was really helpful..

  2. Ditto, just what I needed. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: