Locks are an important part of MSSQL server as they are used to protect the same information being changed by multiple clients at the same time when each of the clients don't expect the information to change at all. Sometimes it is important
to find out what process or user has locks as long running queries can prevent other clients from being able to run queries from the database.

So below are a few queries for producing some basic information about who is locking what and just how many locks have been taken.The following will produce locking information based on SPID, DatabaseName, LoginName and TotalLocks which are being held.

SELECT  L.request_session_id AS SPID,
        DB_NAME(L.resource_database_id) AS DatabaseName,
                ES.login_name AS LoginName,
                COUNT(*) AS TotalLocks
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
                GROUP BY ES.login_name, L.request_session_id, DB_NAME(L.resource_database_id)
                ORDER BY DB_NAME(L.resource_database_id), ES.login_name

In certain environments the code above may not be that useful so the following will list the locks based on hostname instead of login name.

SELECT  L.request_session_id AS SPID,
        DB_NAME(L.resource_database_id) AS DatabaseName,
                ES.host_name AS LoginName,
                COUNT(*) AS TotalLocks
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
                GROUP BY ES.host_name, L.request_session_id, DB_NAME(L.resource_database_id)
                ORDER BY DB_NAME(L.resource_database_id), ES.host_name

The following will give SPID, LoginName, DatabaseName, LockedObjectName and the total locks which are being held.

SELECT  L.request_session_id AS SPID,
                ES.login_name AS LoginName,
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName,
                COUNT(*) AS TotalLocks
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
                GROUP BY ES.login_name, L.request_session_id, DB_NAME(L.resource_database_id), O.Name
               ORDER BY DB_NAME(L.resource_database_id), ES.login_name


Did You find this page useful?

Yes No



Last Modified: 12 December 2016

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - Unsigned int
2011-02-20 - MSSQL - All temp tables are global
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-29 - MSSQL - TRIM Function