Find locked table name with duration and count in SQL Server

Finding LOCK table in SQL Server is one of essential task of DBA. Locked table for long time in SQL Server adversary affect on performance of the application, especially in pick business hours.

I have one SQL Server VIEW which help us to find all the table which are being locked currently along with duration since it is being locked.

[sourcecode language=”sql”]
CREATE VIEW WhatIsLocked
AS
SELECT
Locks.request_session_id AS SessionID,
Obj.Name AS LockedObjectName,
DATEDIFF(second,ActTra.Transaction_begin_time, GETDATE()) AS Duration,
ActTra.Transaction_begin_time,
COUNT(*) AS Locks
FROM    sys.dm_tran_locks Locks
JOIN sys.partitions Parti ON Parti.hobt_id = Locks.resource_associated_entity_id
JOIN sys.objects Obj ON Obj.object_id = Parti.object_id

JOIN sys.dm_exec_sessions ExeSess ON ExeSess.session_id = Locks.request_session_id
JOIN sys.dm_tran_session_transactions TranSess ON ExeSess.session_id = TranSess.session_id
JOIN sys.dm_tran_active_transactions ActTra ON TranSess.transaction_id = ActTra.transaction_id
WHERE   resource_database_id = db_id() AND Obj.Type = ‘U’
GROUP BY ActTra.Transaction_begin_time,Locks.request_session_id, Obj.Name
[/sourcecode]

You can use this view to keep automated watch. you can query this view every few minutes in database in business hours and if find lock bigger then certain duration, send email with detail. You can even keep watch by putting this query in web/windows application which keeps refreshing every few seconds and show details of locking.

I have written one more article in past which displays lock table, table with  ‘SCH-M’ lock and which command causing the lock. read article in detail, click here.

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

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah