Retriving a week old deadlocks in SQL Server

0
votes

Is any possible way to find which process caused a deadlock last week in SQL Server? I checked Extended Events that only keeps data from last night. If anyone can share a script to find out would be helpful.

The job has completed with an error - Database server name: ################- Transaction (Process ID 279) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 279) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

1
sql-serverperformancedeadlockdatabase-deadlocks
This is why you want to set up an explicit trace or event notification yourself to have a permanent record of all deadlocks and their graphs (for example). The default traces are "best effort" and could get overwritten at any time, depending on how busy the server is. They're only good for instant troubleshooting. – Jeroen Mostert

1 Answers

1
votes

It is possible to retrieve deadlocks details from Extended Events -> Sessions -> System_health -> right click on package0.event_file and choose view target data, next you need to sort by name to see the deadlock report in XML form and also as graphs. But for this you need to enable trace 1204 and 1222 prior to the deadlocks occurrence.

enter image description here

These are the trace that you need to enable.

DBCC TRACEON  (1204,-1)
DBCC TRACEON  (1222,-1)

Click here for more details...