Profiler Trace – Deadlock Victim and aborted transaction

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.


Start SQL Server Profiler trace General – saving the file to disk

Profiler Trace bild1


Events Selection – select Show all events & Show all columns

Profiler Trace bild2


Events Selection – select Locks, select Deadlock graph

Profiler Trace bild3


Select the Column Filters, select Database Name. Enter the database

Profiler Trace bild4


Select Duration, 3000 (3 seconds) to not collect too much information

Profiler Trace bild5


Events Extraction Settings – choose the path of Deadlock XML file

Profiler Trace bild6


Analyzes, deadlock Chain between the SPID 94 and SPID 56

Profiler Trace bild7

Mark Deadlock graph to display the graph below

Profiler Trace bild8


Analyze the processes further in Deadlock XML Events with XML Notepad, available for download.
Visual Studio also works well.

Profiler Trace bild9

Profiler Trace bild10


Using SQL Server Profiler trace and deadlock graph, we could identify that there were two processes that gave rise to deadlock events. The reason no rollback was performed due to the SPID 94 from ServerX and SPID 56 from ServerX was that they performed the same transaction. The customer had previously migrated application to a new server and either had then forgotten to disable part of the application on the old server or someone turned it on by mistake. After the customer shut down the old server the deadlock events ceased.