Professional Microsoft® SQL Server® 2008 Administration

4.3. Trace Flags

Trace flags give you advanced mechanisms to tap into hidden SQL Server features and troubleshooting tactics. In some cases, they enable you to override the recommended behavior of SQL Server to turn on features such as network-drive support for database files. In other cases, trace flags can be used to turn on additional monitoring. There is a set of flags that help you diagnose deadlocks. To turn on a trace flag, use the DBCC TRACEON command, followed by the trace you'd like to turn on, as shown here:

DBCC TRACEON (1807)

To turn off the trace, use the DBCC TRACEOFF command. This command is followed by which traces you'd like to turn off (multiple traces can be separated by commas), as shown here:

DBCC TRACEOFF (1807, 3604)

When you turn on a trace, you are turning it on for a single connection by default. For example, if you turn on trace flag 1807, which helps diagnose deadlocks, you can diagnose deadlocks only in the scope of the connection that issued the DBCC TRACEON command. You can also turn on the trace at a server level by issuing the command followed by the −1 switch:

DBCC TRACEON (1807, −1)

Once you have turned on the traces, you're probably going to want to determine whether the trace is actually running. To do this, you can issue the DBCC TRACESTATUS command. One method to issue the command is to interrogate whether a given trace is running:

DBCC TRACESTATUS (3604)

This command would return the following results if the trace is not turned on:

TraceFlag ...