Undocumented Trace Flags

for RuBoard

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.

Table 22-3. A few of SQL Server's undocumented trace flags

Flag	Purpose
1717	Causes new objects being created to be system objects (see the undocumented procedure sp_MS_upd_sysobj_category).
1200	Displays verbose locking info.
1205	Complements flag 1204 (deadlock info) by displaying a stack trace when a deadlock occurs.
1206	Complements flag 1204 by displaying the other locks held by deadlock parties.
1211	Disables lock escalation.
2509	Used in conjunction with DBCC CHECKTABLE to return the total count of ghost records in a table.
3502	Logs extra information to the system error log each time a checkpoint occurs.
3505	Disables automatic "checkpointing."
3607	Skips automatic recovery of all databases.
3608	Skips automatic recovery of all databases except master.
3609	Skips the creation of tempdb at system start-up.
8501	Enables tracing of DTC events.
8602	Disables index hints.
8687	Disables query parallelism.
8722	Disables all other types of hints.
8755	Disables locking hints.

for RuBoard