Category Archives: sys.dm_tran_active_transactions

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.

	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

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