How to select deadlock history in last 7 days

Question

Hello,

How can I find database wise deadlock history in last 7 days?

Thanks in advance.

Answer 1

Olaf is wrong - deadlocks is one of the things that SQL Server tracks out of the box. Or more precisely from SQL 2012 and on. For older version, you need to set up your own tracing.

Use this query on SQL 2017 and later:

   SELECT CAST(event_data AS xml), timestamp_utc  
   FROM sys.fn_xe_file_target_read_file(  
        N'system_health*.xel', DEFAULT, DEFAULT, DEFAULT)  
   WHERE object_name = 'xml_deadlock_report'  
      AND  timestamp_utc > dateadd(WEEK, -1, sysutcdatetime())  
   ORDER BY timestamp_utc DESC  

For SQL 2012 to SQL 2016

   ; WITH CTE AS (  
     SELECT CAST(event_data AS xml) AS xml  
     FROM sys.fn_xe_file_target_read_file(  
        N'system_health*.xel', DEFAULT, DEFAULT, DEFAULT)  
     WHERE object_name = 'xml_deadlock_report'  
   )  
   SELECT xml.query('/event/data[1]/value[1]/deadlock[1]'),   
          xml.value('/event[1]/@timestamp', 'datetime2(3)') AS timestamp_utc  
   FROM   CTE  
   WHERE timestamp_utc > dateadd(WEEK, -1, sysutcdatetime())  
   ORDER BY timestamp_utc DESC  

Answer 2

If you haven't setup an audit/logging, then you can't find a history.