Find Locked Table in SQL Server

sql-server sql-server-2008 sql-server-2005 sql-server-2012 sql-server-2008-r2
user47957 picture user47957 · Mar 1, 2009 · Viewed 140.9k times · Source

How can we find which table is locked in the database? Please, suggest.

Answer

Mitch Wheat picture Mitch Wheat · Mar 1, 2009

You can use sp_lock (and sp_lock2), but in SQL Server 2005 onwards this is being deprecated in favour of querying sys.dm_tran_locks:

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

Related questions

What is the difference between a primary key and a surrogate key?

I googled a lot, but I did not find the exact straight forward answer with an example. Any example for this would be more helpful.

Read More
How do I find running Traces in SQL Server?

Is there an easy way to determine what traces have been set up by sp_trace_create on SQL Server 2000? How about for SQL Server 2005, 2008, 2012, or 2014?

Read More
How to avoid the "divide by zero" error in SQL?

I have this error message: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. What is the best way to write SQL code so that I will never see this error message again? I could do either of the following: …

Read More