Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Apr 2 '13 at 0:13

This question came from our site for professional and enthusiast programmers.

2  
Have you looked at DMVs like sys.dm_tran_locks? Where have you looked? –  Aaron Bertrand Apr 1 '13 at 21:20

1 Answer 1

up vote 5 down vote accepted

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

share|improve this answer
2  
I flagged on purpose a comment/question of mine I did on the Tavern a few days ago which was ignored. The flag got ignored, too. I had no idea that flags are treated differently on the Tavern. –  ypercube Apr 1 '13 at 22:29
3  
@JNK And so says all of us. –  Mark Storey-Smith Apr 1 '13 at 22:35
    
@AaronBertrand I hope you can read later all the discussion in the Heap. –  ypercube Apr 1 '13 at 23:07
    
I I just noticed that the above answer gives the number of locks on the table but does not give locks on pages and entities on that table. Is there a way to get those locks as well? –  Abdul Rahman Apr 5 '13 at 21:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.