O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required