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

trace_flag_1222_1

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.

trace_flag_1222_2

Leave a Reply

Join the Conversation

1 Comment

  1. Pingback: DotNetShoutout
Leave a comment

Your email address will not be published. Required fields are marked *

*


x

Related Posts

SQL 2012 DTA Engine Crashes on Windows 8
Question: Some time back I wrote an article on the new feature that’s added to SQL Server 2012 DTA (database tuning advisor). I have installed S...
sys.dm_db_stats_properties DMV
Service pack 1 for SQL Server 2012 has been released on 9th Nov 2012. It took couple of weeks for me to install it in our servers. Before I settl...
SQL Server 2012 Performance Dashboard Report
Performance dashboard is developed for SQL Server 2005 which provides useful information about the server, in simple it will give you an performa...
powered by RelatedPosts