select dtl.resource_type, db_name(dtl.resource_database_id) as database_name, case when dtl.resource_type in ('DATABASE', 'FILE', 'METADATA') then dtl.resource_type when dtl.resource_type = 'OBJECT' then object_name(dtl.resource_associated_entity_id) when dtl.resource_type in ('KEY', 'PAGE', 'RID') then (select object_name(object_id) from sys.partitions where sys.partitions.hobt_id = dtl.resource_associated_entity_id) else 'Unidentified' end as requested_object_name, dtl.resource_associated_entity_id, dtl.request_mode, dtl.request_status, dowt.wait_duration_ms, dowt.wait_type, dowt.session_id as blocked_session_id, des_blocked.login_name as blocked_user, dest_blocked.text as blocked_command, dowt.blocking_session_id, des_blocking.login_name as blocking_user, dest_blocking.text as blocking_command, dowt.resource_description from sys.dm_tran_locks dtl inner join sys.dm_os_waiting_tasks dowt on dtl.request_session_id = dowt.blocking_session_id inner join sys.dm_exec_requests der on dowt.session_id = der.session_id inner join sys.dm_exec_sessions des_blocked on dowt.session_id = des_blocked.session_id inner join sys.dm_exec_sessions des_blocking on dowt.blocking_session_id = des_blocking.session_id inner join sys.dm_exec_connections dec on dtl.request_session_id = dec.most_recent_session_id cross apply sys.dm_exec_sql_text(dec.most_recent_sql_handle) as dest_blocking cross apply sys.dm_exec_sql_text(der.sql_handle) as dest_blocked where db_name(dtl.resource_database_id) = 'dbdocs' --Replace this with your DBNAME and dtl.resource_associated_entity_id > 0 and dtl.resource_type <> 'DATABASE';
Column | Description --------------------- | ------------------------------------------------------- resource_type | The resource type being locked, File, Object, Page, Key, RID. database_name | The database name where the lock is being held. requested_object_name | The object name of the resource being locked. request_mode | Request mode for the granted or requested lock. request_status | Status of the request. Granted, Convert, or Wait. wait_duration_ms | Total wait time for thsi wait type. wait_type | Name of the wait type. blocked_session_id | ID of the session that is being blocked. blocked_user | Login of user being blocked. blocked_command | Query of the user being blocked. blocking_session_id | ID of the session that is blocking the request. If NULL, not blocked. blocking_user | Login of the user blocking the request. blocking_command | Query of the user blocking the request. resource_description | Description of the resource being consumed.
--Create a table CREATE TABLE dbdocs_Users ( ID INT PRIMARY KEY, Name NVARCHAR(50) ); --Insert few rows into the table INSERT INTO dbdocs_Users (ID, Name) VALUES (1, 'John'); INSERT INTO dbdocs_Users (ID, Name) VALUES (2, 'Smith'); INSERT INTO dbdocs_Users (ID, Name) VALUES (3, 'David'); INSERT INTO dbdocs_Users (ID, Name) VALUES (4, 'Thomas'); INSERT INTO dbdocs_Users (ID, Name) VALUES (5, 'Joe'); INSERT INTO dbdocs_Users (ID, Name) VALUES (6, 'Mark'); INSERT INTO dbdocs_Users (ID, Name) VALUES (7, 'Brad'); INSERT INTO dbdocs_Users (ID, Name) VALUES (8, 'Trump'); INSERT INTO dbdocs_Users (ID, Name) VALUES (9, 'Claire'); INSERT INTO dbdocs_Users (ID, Name) VALUES (10, 'Nick'); --In frist session BEGIN TRANSACTION; UPDATE dbdocs_Users SET Name = 'Ken' WHERE ID = 2; --In second session BEGIN TRANSACTION; UPDATE dbdocs_Users SET Name = 'Hudson' WHERE ID = 2;