Converting Deadlock Reports to Graphs

Published 06 November 10 05:34 PM | MartinBell

I’d only just posted my blog about persisting deadlock reports from extended events when I had the following email conversation with David Betteridge

David: It sounds like you hit the same issues as me,  the deadlock xml is invalid until you apply CU1 to 2008 R2.  Only then you discover that xml format isn’t the same as a .xdl file!

Me: Yes, maybe the next thing to do is work out a transformation to convert it.

David: The main difference seems to be that profiler only supports a single deadlock victim and so includes the victim’s processID in deadlock element

<deadlock victim="process7d2988">

However,  extended events now support multiple victims and has a slightly different structure


    <victimProcess id="process13f708" />


I should be able to write an xslt to transform one structure to another without too much hassle.  Then it’s just a question of working out how to call it.  Maybe powershell?

And low and behold in my inbox the next day I get the following email from David:

Attached is an xslt and powershell script which will convert a deadlock file generated by extended events into a format suitable for display within SSMS.

Steps are…

· Get the deadlock_report from the ring buffer

· Save the xml as a file on the local disk.

· Run the attached powershell script and pass in the full path as a parameter

· It generates a new file with a .xdl extension.

It worked on my single test case!

I checked my test case and it certainly does work, providing you have the cumulative updates that produce valid XML. When you have multiple victims it will only display one victim.

To run this from a command prompt use the command:

powershell –file convert.ps1 <xml_file>

You will need an execution policy that will allow scripts to be run (hopefully not unrestricted!). You will either need to add a path to the location of transform.xslt or execute the script in the same directory. If you want to read more about the changes to the deadlock graphs in Extended Events check out Jonathan's post.

Here are David’s files.



No Comments

This Blog

SQL Blogs