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

14 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
  5. Thank you very much. Really appreciate this.

    ReplyDelete
  6. Thank you very much. This was helpful.

    ReplyDelete
  7. Thank you very much Aasim. You are the man.

    ReplyDelete
  8. Hi,
    Can you please tell me whether it works for AZURE Databases.
    I am getting zero rows for both the queries.

    Thanks in advance

    ReplyDelete
  9. sir i need sql server data replicate in mysql db, how to solve this my problem?

    ReplyDelete
  10. Why exclude resource_type DATABASE, what does this accomplish? When I include these types I see a lot more data (more connections?). I want to kill all connections for a specific user.

    ReplyDelete

All suggestions are welcome