Skip to content

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

SQL Server

Dan View All

Blog owner.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: