Finding LOCK table in SQL Server is one of essential task of DBA. Locked table for long time in SQL Server adversary affect on performance of the application, especially in pick business hours.
I have one SQL Server VIEW which help us to find all the table which are being locked currently along with duration since it is being locked.
CREATE VIEW WhatIsLocked
Locks.request_session_id AS SessionID,
Obj.Name AS LockedObjectName,
DATEDIFF(second,ActTra.Transaction_begin_time, GETDATE()) AS Duration,
COUNT(*) AS Locks
FROM sys.dm_tran_locks Locks
JOIN sys.partitions Parti ON Parti.hobt_id = Locks.resource_associated_entity_id
JOIN sys.objects Obj ON Obj.object_id = Parti.object_id
JOIN sys.dm_exec_sessions ExeSess ON ExeSess.session_id = Locks.request_session_id
JOIN sys.dm_tran_session_transactions TranSess ON ExeSess.session_id = TranSess.session_id
JOIN sys.dm_tran_active_transactions ActTra ON TranSess.transaction_id = ActTra.transaction_id
WHERE resource_database_id = db_id() AND Obj.Type = ‘U’
GROUP BY ActTra.Transaction_begin_time,Locks.request_session_id, Obj.Name
You can use this view to keep automated watch. you can query this view every few minutes in database in business hours and if find lock bigger then certain duration, send email with detail. You can even keep watch by putting this query in web/windows application which keeps refreshing every few seconds and show details of locking.
I have written one more article in past which displays lock table, table with ‘SCH-M’ lock and which command causing the lock. read article in detail, click here.
Note: Microsoft Books online is a default reference of all articles but examples and explanations