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!)
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