Retriving a week old deadlocks in SQL Server

0

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.

sql-server
performance
deadlock
database-deadlocks
asked on Stack Overflow Apr 6, 2018 by hassan.h • edited Apr 6, 2018 by James Z

1 Answer

1

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...

answered on Stack Overflow Apr 6, 2018 by John VM

User contributions licensed under CC BY-SA 3.0