0

I want to create a procedure which stores weekly data of how many deadlocks occurred in the database, and store this output in a table - which is the best method to do so?

I tried using extended log but how can I store that XML output in the table?

Thank you in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hima Joshi
  • 11
  • 3

1 Answers1

0

The best way to do it is to create a profiler trace for the database. You should look at filtering what deadlocks (for example by app or database or user) are captured. After whatever period you run the trace you can import the data into a table using the fn_trace method:

SELECT * INTO tracetable

FROM ::fn_trace_gettable('c:\deadlock.trc', default)
benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22
  • thank you for providing solutions. I tried but there is a number of rows how can I pick up the only deadlock related? – Hima Joshi Jun 20 '18 at 09:37
  • Please follow this article on exactly how to set up a deadlock trace: https://www.red-gate.com/simple-talk/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/. You need additional events to really understand what let to the deadlock. But what many articles don't emphasize is the importance of setting up filters on the trace - otherwise you can have way too much to analyze. Please mark the answer as correct it you feel I answered the question. – benjamin moskovits Jun 20 '18 at 13:37