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 quries can prevent other tasks from being able to retrive data from the database.
So below are a few quries 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 enviroments the code above may not be that useful so the following will list the locks based on hostname instead of loginname.
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