Detect and Kill Session/ Exclusively Locked Session in SQL Server using SQL

This following query retrieves active Sessions in specific Database.


-- Get Active Sessions 
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 ='Your Database Name'
ORDER BY name

This one retrieves Exclusively Locked Sessions.


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 ='Your Database Name'
ORDER BY name 

You can kill process using corresponding session_id if requires.


-- Kill associated session id (For Example 56)
KILL 56

Thanks
A Rahim Khan

Advertisements
    • suhail
    • January 26th, 2012

    sir
    i want to delete row of sql table from datagridview using C# and sql server
    plz provide me code
    thanks and regards
    suhail

  1. No trackbacks yet.

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: