Who’s Locking The Table?
This is the script I use to view who/what is locking the table in SQL Server:
SELECT l.request_session_id AS SessionID, o.Name AS 'Table Locked', DATEDIFF(second, at.Transaction_begin_time, GETDATE()) AS 'Duration (Seconds)', FORMAT(at.Transaction_begin_time, 'yyyy-mm-dd h:mm:ss tt') 'Started On', es.login_name 'User', es.[program_name] 'Program', COUNT(*) AS Locks 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_tran_session_transactions st ON es.session_id = st.session_id JOIN sys.dm_tran_active_transactions at ON st.transaction_id = at.transaction_id WHERE resource_database_id = DB_ID() AND o.Type = 'U' GROUP BY at.Transaction_begin_time, l.request_session_id, o.Name, es.login_name, [program_name]
Categories