Day 12–Trace Flag 1222–Capture Deadlock information in XML like format

Yesterday we saw the trace flag 1204 which will give you the information based on nodes. Today we are going to get similar deadlock information using trace flag 1222 in XML like format which means the format does not conform to an XML Schema Definition (XSD) schema. After you enable the trace flag when an deadlock happens it will pushes the information to errorlog you can get the deadlock information from errorlog .

I’m going to enable trace flag in global scoped and I’m going to use the same script which we used yesterday to recreate the deadlock, I’ve changed only the trace flag number other than that everything remains same.

--Enabling 1222 Deadlock trace flag
DBCC TRACEON(1222,-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

Now let’s pull out the deadlock information from errorlog which will be in XML like format as shown below. It has 3 sections, first section gives you information about deadlock victim, second section describes each processes involved in deadlock and third section describes the resources list that involved in deadlock. You can use any of these trace flag which is comfortable to you.