Day 11–Trace Flag 1204– Capture Deadlock information focused on nodes

Have you ever wonder to capture Deadlock information? If so we have wonderful trace flags available in SQL server which will give you bunch of information about deadlock information. Today we are going to see one of the trace flag 1204 which will capture deadlock information focused on two nodes that is involved in deadlock.

What does it give. It will provide you deadlock information focused on nodes, it has three sections, first two sections is dedicated to the nodes and the final section describes the deadlock victim. We will do a test to capture the deadlock information. I’m going to run the below command in different connections to create the deadlock and also I’m going to enable the trace flag as globally scoped one. For more on enabling trace flag refer my article “Enable \ Disable Trace flags in SQL Server”.

--Enabling 1204 Deadlock trace flag
DBCC TRACEON(1204,-1)
GO
--Run in first connection
USE TEMPDB
GO
CREATE TABLE deadock_table1 (age INT)
INSERT deadock_table1 VALUES(26)
CREATE TABLE deadock_table2 (age INT)
INSERT deadock_table2 VALUES(27)
BEGIN TRAN
UPDATE deadock_table1 SET age = 30
--Run in second connection
USE tempdb
BEGIN TRAN
UPDATE deadock_table2 SET age = 12
UPDATE deadock_table1 SET age = 68
--Run in first connection
UPDATE deadock_table2 SET age = 45

From the image above you can see that the one of the process has been chosen as deadlock victim to know the node details check out errorlog.

Now from the image above you can see that first two sections are dedicated for the nodes involved in deadlock and it gives you the details about query it is running. Last section gives you the information about deadlock victim and here it’s SPID 53. We have some more trace flags to troubleshoot deadlock , let’s discuss it in next article.