Getting the deadlock details

In SQL 2012, you may query the deadlock details with the following SQL statement:

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;


The following query is for SQL2008:

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 )


For more details about deadlock and resolution, please read the following article:

https://www.red-gate.com/simple-talk/sql/database-administration/handling-deadlocks-in-sql-server/
https://www.red-gate.com/products/dba/sql-monitor/resources/articles/monitor-sql-deadlock