patternsqlModerate

Find out number of active locks on a table

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

numberactivefindlockstableout

Problem

I was wondering if there is a way to find out the number of active locks held on a table in SQL Server?

Solution

Here is a start. Remember that locks can go parallel so you may see the same object being locked on multiple resource_lock_partition values.

USE yourdatabase;
GO

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename');

Please look at the documentation for sys.dm_tran_locks

Code Snippets

USE yourdatabase;
GO

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename');

Context

StackExchange Database Administrators Q#39026, answer score: 12

Revisions (0)

No revisions yet.