How to select deadlock history in last 7 days

Question

How to select deadlock history in last 7 days

sourav dutta 231

Hello,

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

Thanks in advance.

Accepted answer

1 additional answer

Your answer

Answer 1

Erland Sommarskog 121.9K MVP Volunteer Moderator

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

sourav dutta 231 Reputation points

2022-07-06T09:29:04.917+00:00

Thank you. It works.

Is there any possible way to get this result database wise?

Erland Sommarskog 121.9K MVP Volunteer Moderator

You can use this query. However, it's a little precarious in the general case, since a deadlock could comprise tables in more than one database.

   ; WITH CTE AS (  
     SELECT CAST(event_data AS xml) 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'  
   ), deadlock AS (  
      SELECT xml.query('/event/data[1]/value[1]/deadlock[1]') AS deadlock,   
             timestamp_utc  
      FROM   CTE  
   )  
   SELECT deadlock,   
          deadlock.value('/deadlock[1]/process-list[1]/process[1]/@currentdbname',  
                         'nvarchar(128)') AS dbname,   
          timestamp_utc  
   FROM   deadlock  
   ORDER BY timestamp_utc DESC

sourav dutta 231 Reputation points

2022-07-06T12:19:01.06+00:00

It is working.

Thanks a lot.

Answer 2

Olaf Helper 47,516

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

Share via

How to select deadlock history in last 7 days

1 additional answer

Your answer