SQL SERVER – How to Identify Locked Table in SQL Server?

Here is a quick script which will help users to identify locked tables in the SQL Server.

SELECT
OBJECT_NAME(p.OBJECT_ID) AS TableName,
resource_type, resource_description
FROM
sys.dm_tran_locks l
JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id

When you run above script, it will display table name and lock on it.

I have written code to lock the person table in the database. After locking the database, when I ran above script – it gave us following resultset.

SQL SERVER - How to Identify Locked Table in SQL Server? lock

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Unable to Bring SQL Cluster Resource Online – Online Pending and then Failed
Next Post
Interview Question of the Week #032 – Best Practices Around FileGroups

Related Posts

No results found.

10 Comments. Leave new

  • Allen Cunningham
    August 15, 2015 7:38 am

    Pinal – What does resource_description mean?

    Reply
  • How do you lock a single table in database?

    Reply
    • create table Table1(a int)
      begin tran
      insert Table1 values(1)

      Until the transaction will be committed or rolled back, the table Table1 will be locked.

      Reply
      • Manioko – I see, its locking table only for SELECT and UPDATE but for INSERT. it does not stop any one to INSERT data in table.
        I want to completely lock the table and not allow anyone do any operation on that table? One way is write a trigger on table and ROLLBACK everything.

      • Looking for TABLOCKX hint, it will help you

  • Thanks Dave, Hopefully I’ll never have to use this, but it’ll be a handy tool in the ol’ toolbox.

    Reply
  • Hi Pinal,

    I think your code only finds locks in a particular database and not across all databases on the server? If this assumption is correct, I would like to know how to find all locked tables and views across all databases on a given server?

    Thanks,
    Vlad.

    Reply
  • How can I know which of user has locked the Table and from which host computer?

    Reply
  • priya.bhavanasi@gmail.com
    October 28, 2020 2:45 pm

    what is resource_description here?

    Reply

Leave a Reply

Menu