The following T-SQL query uses sys.dm_tran_locks in order to return useful information:
SELECT
resource_type,
resource_database_id,
(select [name] from master.sys.databases where database_id =resource_database_id) as [DBName],
(case resource_type when 'OBJECT' then object_name(resource_associated_entity_id,resource_database_id) else NULL end) as ObjectName,
resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description
FROM sys.dm_tran_locks
A sample output of the above query would look like the one below:
An alternative way of monitoring the active locks is to use the “Activity Monitor” module in SQL Server Management Studio (SSMS) 2005 or later.
By joining the records returned by sys.dm_tran_locks and sys.dm_os_waiting_tasks DMVs you can get blocking information:
SELECT
t1.resource_type,
t1.resource_database_id,
(select [name] from master.sys.databases where database_id =resource_database_id) as [DBName],
t1.resource_associated_entity_id,
(case resource_type when 'OBJECT' then object_name(resource_associated_entity_id,resource_database_id) else NULL end) as ObjectName,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1, sys.dm_os_waiting_tasks as t2
WHERE t1.lock_owner_address = t2.resource_address
The above two queries provide you with information on active locks and blocking cases and can help you identify incidents that might need manual intervention (i.e. in cases of a bad database design and when the database is accessed concurrently).