We capture DeadLock data via Event Notification (Based off  What changed? Auditing solutions in SQL Server – Colleen Morrow in Tribal SQL ). Capturing the XML is great but obviously leads to the annoyance of if you want to read a graph you really want it in a XDL file not XML. As a solution today I created a PowerShell function to grab the XML from a table and export all the deadlocks to graphs (Based on the server and event time).

Its kind of specific to our environment so interested in a changes that i could make to open it more (let me know!)

Example Usage:

dlFunction01

If excepts the following parameters:

  • SQLInstance (Can be single or mulitple, or from the pipeline)
  • Table (Name of the table that you log deadlocks to)
  • Path (Where the .xdl files are created)
  • Force (overwrite existing files if they exist)

Hopefully its useful to others, and the source code can be found on GitHub at: https://github.com/Staggerlee011/PowerShell/blob/master/SQLServer/Export-XDLFile.ps1

Advertisements