Monday, January 21, 2013

Trace Flag 1222 - DBCC TRACEON (1222, -1)

Trace Flag 1222 allows you to diagnose deadlocks.

DBCC TRACEON 1222 - (from MSDN)
"Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema "
Setting as a server startup option is done via the SQL Server Configuration Manager.
In SQL 2008 they are done from the Advanced tab of the Service properties as shown here.
From SQL 2012 they have their own tab ...


Turning it on & off via TSQL is as easy as these commands -

At session level - 
  • DBCC TRACEON (1222,)
  • DBCC TRACEOFF (1222)

    At server level - 
  • DBCC TRACEON (1222, -1)
  • DBCC TRACEOFF (1222, -1)
My development background means I like the idea of placing commands in a stored procedure that is executed on startup. Much nicer and more maintainable (for me) than messing service properties.
@MsSqlGirl Julie Koesmarno has done exactly that, here.

You can check which flags are enabled by using DBCC TRACESTATUS .

Links :

Finding SQL Server Deadlocks Using Trace Flag 1222
MSDN : trace Flags
Technet : DBCC TraceOn
Turn On Deadlock Trace Flag
How do I work with Trace Flags
SQL Server 2012 Startup Parameters

No comments: