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