select dtl.request_session_id, db_name(dtl.resource_database_id) as database_name, dtl.resource_type, 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, dtl.resource_database_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.request_mode, dtl.request_status, der.blocking_session_id, des.login_name, dec.client_net_address, dtl.resource_description, case dtl.request_lifetime when 0 then dest_r.text else dest_c.text end as statement from sys.dm_tran_locks dtl left outer join sys.dm_exec_requests der on dtl.request_session_id = der.session_id inner join sys.dm_exec_sessions des on dtl.request_session_id = des.session_id inner join sys.dm_exec_connections dec on dtl.request_session_id = dec.most_recent_session_id outer apply sys.dm_exec_sql_text(dec.most_recent_sql_handle) as dest_c outer apply sys.dm_exec_sql_text(der.sql_handle) as dest_r where dtl.resource_type <> 'DATABASE' and db_name(dtl.resource_database_id) = 'dbdocs' -- Replace this with your DBNAME order by dtl.request_session_id;
Column | Description --------------------- | ------------------------------------------------------- request_session_id | Session ID that currently owns this request. database_name | The database name where the lock is being held. resource_type | Request type. 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. blocking_session_id | ID of the session that is blocking this request. If NULL, not blocked. login_name | Login of the user who owns this request. client_net_address | IP address of the connection making the request. resource_description | Description of the resource being consumed. statement | Query making the request.
--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;