SQL Server Find Table Locks

Sep 24, 2024

Related to:: SQL Server

We can use the following query to list all the current locks in the database.

SELECT
    OBJECT_NAME(P.object_id) AS table_name,
    resource_type,
    request_session_id
FROM
    sys.dm_tran_locks L
JOIN
    sys.partitions P ON L.resource_associated_entity_id = p.hobt_id
WHERE   
    OBJECT_NAME(P.object_id) = 'myTableName'

This will give you something like this:

table_name	resource_type	request_session_id
jobs	PAGE	477
jobs	KEY	477

If we need (for some reason) to manually remove the locks, we can just get the session ID and kill it using kill session_id.

Ex:

kill 477

Understanding why we have the locks before manually removing them is crucial to avoid the same problems in the future.

References §

• sys.dm_tran_locks (Transact-SQL) - SQL Server