Monthly Archives: December 2016

Gather Deadlock Information by useing DM views

SQL 2008

;WITH Deadlock
as
(
SELECT  CAST(event_data.value('(event/data/value)[1]',
                               'varchar(max)') AS XML) AS DeadlockGraph
FROM    ( SELECT    XEvent.query('.') AS event_data
          FROM      (    -- Cast the target_data to XML 
                      SELECT    CAST(target_data AS XML) AS TargetData
                      FROM      sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s
                                 ON s.address = st.event_session_address
                      WHERE     name = 'system_health'
                                AND target_name = 'ring_buffer'
                    ) AS Data -- Split out the Event Nodes 
                    CROSS APPLY TargetData.nodes('RingBufferTarget/
                                     event[@name="xml_deadlock_report"]')
                    AS XEventData ( XEvent )
        ) AS tab ( event_data ) 
)
select 
deadlockgraph.value('/deadlock[1]/process-list[1]/process[1]/@lasttranstarted','datetime') as lasttranstarted
,DeadlockGraph
 from deadlock

SQL 2012

;WITH Deadlock
as
(
SELECT  XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM    ( SELECT    XEvent.query('.') AS XEvent
          FROM      ( SELECT    CAST(target_data AS XML) AS TargetData
                      FROM      sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s
                                 ON s.address = st.event_session_address
                      WHERE     s.name = 'system_health'
                                AND st.target_name = 'ring_buffer'
                    ) AS Data
                    CROSS APPLY TargetData.nodes
                  ('RingBufferTarget/event[@name="xml_deadlock_report"]')
                    AS XEventData ( XEvent )
        ) AS src        
)
select 
deadlockgraph.value('/deadlock[1]/process-list[1]/process[1]/@lasttranstarted','datetime') as lasttranstarted
,DeadlockGraph
 from deadlock
 

see for more information about this topic url: https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/