Capturing SQL Server Deadlock Information in XML Format


By:   |   Updated: 2007-04-30   |   Comments (1)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Locking and Blocking

Problem

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?

Solution

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

SQL Server Profiler - Events Selection Tab

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

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.

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.

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.

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. 

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


Last Updated: 2007-04-30


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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

 

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools