Lock information in SQL Server

The script gets miscellaneous information about locks including SQL.

  L.request_session_id              AS SPID, 
  DB_NAME(L.resource_database_id)   AS DatabaseName,
  L.request_mode                    AS LockType,
  L.request_status                  AS RequestStatus,
  L.resource_type                   AS ResourceType,
  L.resource_description            AS ResourceDescription,
  L.resource_associated_entity_id   AS ResourceAssociatedEntryId,
  -- Object
  O.name                            AS ObjectName, 
  O.object_id                       AS ObjectId,
  -- SQL
  ST.text                           AS SqlStatementText,
  CN.auth_scheme                    AS AuthenticationMethod,
  -- Session
  ES.login_name                     AS LoginName,
  ES.host_name                      AS HostName,
  -- Transaction
  TST.is_user_transaction           AS IsUserTransaction,
  AT.name                           AS TransactionName,
  AT.transaction_begin_time         AS TransactionBeginTime
  sys.dm_tran_locks L
  -- Object
  LEFT JOIN sys.objects O ON O.object_id = (
      WHEN resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') 
        THEN (
          SELECT P.object_id 
          FROM sys.partitions P 
          WHERE P.hobt_id = L.resource_associated_entity_id
      WHEN resource_type = 'OBJECT' 
        THEN L.resource_associated_entity_id
      WHEN resource_type = 'ALLOCATION_UNIT'     
        THEN (
          SELECT P.object_id FROM sys.partitions P 
          INNER JOIN sys.allocation_units AU
          ON AU.container_id = (
              WHEN AU.type IN (1,3) THEN P.hobt_id
              WHEN AU.type = 2 THEN P.partition_id
          WHERE AU.allocation_unit_id = L.resource_associated_entity_id
  -- SQL
  LEFT JOIN sys.dm_exec_connections CN 
  ON CN.session_id = L.request_session_id
  CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
  -- Session
  LEFT JOIN sys.dm_exec_sessions ES 
  ON ES.session_id = L.request_session_id
  -- Transaction
  LEFT JOIN sys.dm_tran_session_transactions TST 
  ON TST.session_id = L.request_session_id
  LEFT JOIN sys.dm_tran_active_transactions AT 
  ON AT.transaction_id = TST.transaction_id 

1 thought on “Lock information in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s