How to select deadlock history in last 7 days

sourav dutta 231 Reputation points
2022-07-06T06:36:10.637+00:00

Hello,

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

Thanks in advance.

Developer technologies | Transact-SQL
SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2022-07-06T08:31:01.2+00:00

    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  
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 47,516 Reputation points
    2022-07-06T07:07:22.957+00:00

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

    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.