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.