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





Comments For This Article




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


Recommended Reading

Finding and troubleshooting SQL Server deadlocks

SQL Server Deadlock Priority Configuration

SQL Server Profiler Graphical Deadlock Chain

Using a Clustered Index to Solve a SQL Server Deadlock Issue

Implementing SQL Server Transaction Retry Logic for failed transactions





get free sql tips
agree to terms


Learn more about SQL Server tools