DBA Handy ToolsUncategorized

DeadLock Finding – SQL Script

DeadLock On SQL:

Occasionally you may find deadlock on the SQL Server. If you have enabled the trace, it will appear in the error log. Alternatively you can use the below script to see. Tested on SQL Server 2012

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;


 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *