Trace flags in SQL Server are used to switch ON/OFF specific SQL Server features temporarily. A trace flag can be a global or a session only. A global trace flag effects all sessions whereas session only effects particular session. An example of global Trace flag is 1222, which deadlock information for all sessions in xml format. An example of session trace flag is 1211 which is used to disable lock escalation.
A list of trace flags can be found here
A T-SQL to enable/disable trace flags is given below.
A trace flag can be set to ON using DBCC TRACEON command and is set to OFF using DBCC TRACEOFF command. DBCC TRACESSTATUS command is used to check the current status of a particular trace flag.
Multiple trace flag can be set to ON/OFF as shown below.
DBCC TRACEON(1222,1205)
To check all trace flag enabled for a session, use DBCC TRACESTATUS as shown below.
Follow for detailed trace flag content.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
