Find table being locked in SQL Server 2008/2005

As a DBA one need to keep watch on database for lock. This is really very interesting subject and often useful in many different troubleshooting situation. There are many types of LOCK in database, I will write article on different types of lock very soon, right now my intention is to show which table is being locked at the moment. Sometime you index the table or change the schema of table, table got completely locked at that time. I have one very small yet useful and handy query which can show you which table is locked right now. I have executed the query on AdventureWorks database under SQL Server 2005 environment but the same query is compatible under SQL Server 2005 and SQL Server 2008 both.

use Adventureworks

go

--to list of tables which are being locked at the moment

select

  object_name(resource_associated_entity_id) as 'TableName' ,*

from

  sys.dm_tran_locks

where resource_type = 'OBJECT'

  and resource_database_id = DB_ID()

GO

--find complete table lock with  'SCH-M' request mode

--generally when you rebuld index, table is being locked

--and unaccesible, it has schema mode change lock ('SCH-M') mode

--so, find out those table which is unavailable

select

  object_name(resource_associated_entity_id) as 'TableName' ,*

from

  sys.dm_tran_locks

where resource_type = 'OBJECT'

  and resource_database_id = DB_ID()

   and request_mode = 'Sch-M'

GO

--one more beautiful query which can show you which command is causing problem.

--if rebuild index causing complete table lock than you will have DBCC in command column

select

  object_name(sl.resource_associated_entity_id) as 'TableName' ,dr.command,sl.*

from

  sys.dm_tran_locks as sl left join sys.dm_exec_requests dr on sl.request_session_id=dr.session_id

where sl.resource_type = 'OBJECT'

  and sl.resource_database_id = DB_ID()

   and sl.request_mode = 'Sch-M'

GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com