mssql sqlserver 通过扩展事件监控数据库中的死锁事件


摘要:
下文讲述使用sql脚本获取,通过扩展事件监控数据库中的死锁事件,如下所示:
实验环境:sql server 2008 R2



 SELECT  xed.value('@timestamp', 'datetime') AS [创建时间] ,
        xed.query('.') AS [扩展事件]
FROM    ( SELECT    CAST([target_data] AS XML) AS Target_Data
          FROM      sys.dm_xe_session_targets AS xt
                    INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
          WHERE     xs.name = N'system_health'
                    AND xt.target_name = N'ring_buffer'
        ) AS XML_Data
        CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')
        AS XEventData ( xed )
ORDER BY [创建时间] DESC