The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

Undocumented Trace Flags

SQL Server trace flags are integer values that you pass to the server to enable special functionality, to provide better diagnostic or system internal info, or to work around problems. You enable trace flags primarily by calling DBCC TRACEON(), but they can also be turned on via the T server command-line option. Some options only make sense on a serverwide basis, so they're best specified on the server command line. Some only make sense within a particular connection, so they're enabled with DBCC DBCC TRACEON(flagnum), where flagnum is the flag to be set. DBCC TRACEOFF() is the counterpart to TRACEON(). It turns off specific trace flags for a connection. Separate multiple flags with commas to set/unset them at once.

DBCC TRACESTATUS(flagnum) shows whether a flag is enabled. Pass 1 to return a list of all currently enabled flags. Here's a simple DBCC TRACEON() / TRACESTATUS() example:

EXEC master..xp_logevent 99999,'CHECKPOINT before 
setting flag 3502',informational
CHECKPOINT
DBCC TRACEON(3604,3502)
DBCC TRACESTATUS(-1)
EXEC master..xp_logevent 99999,'CHECKPOINT after
 setting flag 3502',informational
CHECKPOINT
DBCC TRACEOFF(3604,3502)
DBCC TRACESTATUS(-1)

(Results)

TraceFlag Status
--------- ------
3502      1
3604      1

Here's what the error log looks like as a result of these commands (trace flag 3502 enables extra CHECKPOINT log information).

2000-07-01 01:10:33.89 spid57  Error: 99999, Severity: 10, State: 1
2000-07-01 01:10:33.89 spid57  CHECKPOINT before setting flag 3502.
2000-07-01 01:10:33.97 spid57  DBCC TRACEON 3604,
 server process ID (SPID) 57.
2000-07-01 01:10:34.00 spid57  DBCC TRACEON 3502,
 server process ID (SPID) 57.
2000-07-01 01:10:34.00 spid57  Error: 99999, Severity: 10, State: 1
2000-07-01 01:10:34.00 spid57  CHECKPOINT after setting flag 3502.
2000-07-01 01:10:34.00 spid57  Ckpt dbid 4 started (100000)
2000-07-01 01:10:34.00 spid57  Ckpt dbid 4 phase 1 ended (100000)
2000-07-01 01:10:34.00 spid57  Ckpt dbid 4 complete
2000-07-01 01:10:34.00 spid57  DBCC TRACEOFF 3604,
 server process ID (SPID) 57.
2000-07-01 01:10:34.00 spid57  DBCC TRACEOFF 3502,
 server process ID (SPID) 57.

Table 22-3 lists some of the many undocumented SQL Server trace flags. (See the Books Online for a list of documented flags.) This list is not comprehensive; there are many undocumented flags not included here.