Capturing SQL Server Deadlock Information in XML Format

By:   |   Comments (1)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Locking and Blocking


In your recent tips on SQL Server dead locks (How To: Graphical Deadlock Chain and Deadlock Priority Configuration) I can see the value of using Profiler to capture the process related information.  Do any other options exist in Profiler to capture the information is an easier format that I can review?  If so, what is the format and how can I begin to take advantages of this configuration in Profiler?


SQL Server Profiler has the ability to capture the deadlock related information as XML files which can be analyzed to determine the overall locking and blocking issue.  Capturing this additional information can be beneficial if you experience long locking and blocking chains frequently. 

SQL Server Profiler - General Tab

Specify the name, template and save location (table or file).

SQLServer2005 Profiler General 1

SQL Server Profiler - Events Selection Tab

Specify the deadlock graph, Lock:Deadlock and Lock:Deadlock Chain events in addition to any other counters desired.

SQLServer2005 Profiler EventsSelection 2

SQL Server Profiler - Events Extraction Settings Tab

Enable the 'Deadlock XML' check box and when you are prompted for the file location, browse to the needed directory and provide a file name.  Once you have verified all of the configurations from all 3 tabs, press the 'Run' button to start Profiler.

SQLServer2005 Profiler EventsExtractionSettings

SQL Server Profiler Results

In this example, 2 processes are trying to update the same sets of data in two tables.  One becomes a deadlock and the other succeeds.  From the image below, you can see a portion of the deadlock chain.

SQLServer2005 Profiler DeadlockResults

XML Output

Once the Profiler session is stopped, then go and review the XML file specified on the Events Extraction Settings Tab.  If you open the file with Notepad or XML Notepad, you can review the XML corresponding to the Deadlock chain that occurred.  To download the example XML file click on DeadlockExample.xdl.

SQLServer2005 Profiler XMLDeadlock

Deadlock - Graphical Representation

If you happen to double click on the XML file from above without reviewing it in Notepad (or XML Notepad), by default SQL Server 2005 Management Studio will load the file into a separate query window and give you a graphical representation of the deadlock similar to the Profiler representation. 

SQLServer2005 Profiler GraphicalDeadlock

XML Deadlock File Considerations

  • The XML Deadlocks feature gives you the ability to capture the deadlock related information and review the deadlocks independent of the Profiler interface.
  • Each of the deadlock files can be analyzed in order to determine trends with the deadlocks on your SQL Server.
  • Each deadlock situation can be stored in a separate file for per deadlock analysis.
  • The XML format gives you the ability to programmatically review the deadlocks.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, September 14, 2016 - 1:20:38 AM - Rahil Back To Top (43314)

i have run the trace and i got    deadlock graph and i had found queries running deadlocks after that what to do 

 ResType:LockOwner Stype:'OR' Mode: X SPID:52[BKD12]   ECID:0 Ec:(0x43983588) Value:0x42bdee40 Cost:(0/54)

as i have spid 52 is running delete command 

and spid 51 running update command what to do remove deadlock



get free sql tips
agree to terms