Friday, December 24, 2010

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

6 comments:

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

    ReplyDelete
  2. Ditto, just what I needed. Thanks

    ReplyDelete
  3. This saved my day thank you very much.
    just received an angry call from client that our application hangs and doesn't respond! i used above query to find the culprit and kill. all back to normal. Now i need to find why there was an exclusive lock in the first place

    ReplyDelete
  4. Thank you very much, really helpful.
    Same as above comment, I received many call from clients to complain application hangs and does not work.
    After using your script, everything works as normal. Thanks again.

    ReplyDelete

All suggestions are welcome