mssql sqlserver 查看数据库中所有在WAIT状态的锁


摘要:
下文讲述使用sql脚本获取,数据库中状态为WAIT的锁信息,如下所示:
实验环境:sql server 2008 R2



  SELECT  L1.resource_type ,
        DB_NAME(L1.resource_database_id) AS [数据库名称] ,
        CASE L1.resource_type
          WHEN 'OBJECT'
          THEN OBJECT_NAME(L1.resource_associated_entity_id,
                           L1.resource_database_id)
          WHEN 'DATABASE' THEN 'DATABASE'
          ELSE CASE WHEN L1.resource_database_id = DB_ID()
                    THEN ( SELECT   OBJECT_NAME(object_id,
                                                L1.resource_database_id)
                           FROM     sys.partitions
                           WHERE    hobt_id = L1.resource_associated_entity_id
                         )
                    ELSE NULL
               END
        END AS [对象名称] ,
        L1.resource_description ,
        L1.request_session_id ,
        L1.request_mode ,
        L1.request_status
FROM    sys.dm_tran_locks AS L1
        JOIN sys.dm_tran_locks AS L2 ON L1.resource_associated_entity_id = L2.resource_associated_entity_id
WHERE   L1.request_status <> L2.request_status
        AND ( L1.resource_description = L2.resource_description
              OR ( L1.resource_description IS NULL
                   AND L2.resource_description IS NULL
                 )
            )
ORDER BY L1.resource_database_id ,
        L1.resource_associated_entity_id ,
        L1.request_status ASC;