check deadlocking info in SQL server

 To get deadlocking info in log , one of my practice is:

I enabled the trace flag

  DBCC TRACEON(1204, -1) 

DBCC TRACEON(1222, -1) 


----select deadlocks

SELECT

   xed.value('@timestamp', 'datetime2(3)') as CreationDate,

   cast(xed.query('.').value('(/event/data/value/text())[1]', 'nvarchar(MAX)') AS xml) AS XEvent

FROM

(

   SELECT CAST([target_data] AS XML) AS TargetData

   FROM sys.dm_xe_session_targets AS st

      INNER JOIN sys.dm_xe_sessions AS s

         ON s.address = st.event_session_address

      WHERE s.name = N'system_health'

         AND st.target_name = N'ring_buffer'

) AS Data

CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)

ORDER BY CreationDate DESC


also uses Amit's method https://troubleshootingsql.com/2012/09/06/system-health-session-and-deadlocks/ 




Comments

Popular posts from this blog

Sysaux tablespace is too big

SQL server mdf file modifed date

Developing Nightly DBA PACKAGE for MS SQL SERVER and ORACLE