How to find which session is causing lock

By running the below query we can find that which query is causing to the locking.

SELECT lok.resource_type

,lok.resource_subtype

,DB_NAME(lok.resource_database_id)

,lok.resource_description

,lok.resource_associated_entity_id

,lok.resource_lock_partition

,lok.request_mode

,lok.request_type

,lok.request_status

,lok.request_owner_type

,lok.request_owner_id

,lok.lock_owner_address

,wat.waiting_task_address

,wat.session_id

,wat.exec_context_id

,wat.wait_duration_ms

,wat.wait_type

,wat.resource_address

,wat.blocking_task_address

,wat.blocking_session_id

,wat.blocking_exec_context_id

,wat.resource_description

FROM sys.dm_tran_locks lok

JOIN sys.dm_os_waiting_tasks wat

ON lok.lock_owner_address = wat.resource_address
 

 

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server