SQL Server 7.0 and 2000 Trace Flags

Trace flags are used to temporarily enable or disable specific SQL Server database functions, allowing you to change default database behavior or to observe hidden database behavior, and will remain in effect until they are either manually turned off or SQL  Server is restarted. Keep in mind that most trace flags are undocumented and Microsoft can and will change the behavior of the flag between SQL Server versions, service packs or hot fixes.

Trace flags can either be set upon startup of SQL Server by using the -Ttrace# option upon SQL Server startup, or by using the DBCC TRACEON console command. Either way, the trace flag will be active until SQL Server is restarted or you use the DBCC TRACEOFF console command to turn the trace flag off.

Trace flags can be a good investigation tool to find that hidden problem or determine how something is really behaving, but it is recommended that you carefully investigate the behavior of a undocumented trace flag before you implement it in your environments (currently only trace flags 230, 1204, 2528, and 3205 are documented in SQL  Server 2000 Books Online). A through search of Microsoft’s Knowledge Based articles will help in your investigation the current behavior of some trace flags.

The following table is taken from Transact-SQL Language Reference Guide and explains the behavior of a few of the currently documented and undocumented trace flags available for SQL Server 7.0 and 2000.

-1 Sets trace flags for all client connections, rather than for a single client connection. Because trace flags set using the -T command-line option automatically apply to all connections, this trace flag is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF.
106 Disables line number information for syntax errors.
107 Interprets numbers with a decimal point as float instead of decimal.
205 Report when a statistics-dependent stored procedure is being recompiled as a result of AutoStat.
206 Provides backward compatibility for the setuser statement.
242 Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired.
243 The behavior of SQL Server is now more consistent because nullability checks are made at run time and a nullability violation results in the command terminating and the batch or transaction process continuing.
244 Disables checking for allowed interim constraint violations. By default, SQL Server checks for and allows interim constraint violations. An interim constraint violation is caused by a change that removes the violation such that the constraint is met, all within a single statement and transaction. SQL Server checks for interim constraint violations for self-referencing DELETE statements, INSERT, and multi-row UPDATE statements. This checking requires more work tables. With this trace flag you can disallow interim constraint violations, thus requiring fewer work tables.
257 Will invoke a print algorithm on the XML output before returning it to make the XML result more readable.
260 Prints the versioning information about extended stored procedure dlls.
302 Prints information about whether the statistics page is used, the actual selectivity (if available), and what SQL Server estimated the physical and logical I/O would be for the indexes. Trace flag 302 should be used with trace flag 310 to show the actual join ordering.
310 Prints information about join order. Index selection information is also available in a more readable format using SET SHOWPLAN_ALL, as described in the SET statement.
325 Prints information about the cost of using a non-clustered index or a sort to process an ORDER BY clause.
326 Prints information about the estimated and actual cost of sorts.
330 Enables full output when using the SET SHOWPLAN_ALL option, which gives detailed information about joins.
506 Enforces SQL-92 standards regarding null values for comparisons between variables and parameters. Any comparison of variables and parameters that contain a NULL always results in a NULL.
652 Disables read ahead for the server.
653 Disables read ahead for the current connection.
809 Limits the amount of Lazy Write activity in SQL Server 2000.
1180 Forces allocation to use free pages for text or image data and maintain efficiency of storage.
1200 Prints lock information (the process ID and type of lock requested).
1204 Returns the type of lock participating in the deadlock and the current command affect by the deadlock.
1205 Returns more detailed information about the command being executed at the time of a deadlock.
1206 Used to complement flag 1204 by displaying other locks held by deadlock parties
1609 Turns on the unpacking and checking of remote procedure call (RPC) information in Open Data Services. Used only when applications depend on the old behavior.
1704 Prints information when a temporary table is created or dropped.
1807 Allows you to configure SQL Server with network-based database files.
2505 Prevents DBCC TRACEON 208, SPID 10 errors from appearing in the error log.
2508 Disables parallel non-clustered index checking for DBCC CHECKTABLE.
2509 Used with DBCC CHECKTABLE.html to see the total count of ghost records in a table
2528 Disables parallel checking of objects by DBCC commands.
2701 Sets the @@ERROR system function to 50000 for RAISERROR messages with severity levels of 10 or less. When disabled, sets the @@ERROR system function to 0 for RAISERROR messages with severity levels of 10 or less.
3104 Causes SQL Server to bypass checking for free space.
3111 Cause LogMgr::ValidateBackedupBlock to be skipped during backup and restore operations.
3205 Disables hardware compression for tape drivers.
3222 Disables the read ahead that is used by the recovery operation during roll forward operations.
3502 Prints a message to the log at the start and end of each checkpoint.
3503 Indicates whether the checkpoint at the end of automatic recovery was skipped for a database (this applies only to read-only databases).
3602 Records all error and warning messages sent to the client.
3604 Sends trace output to the client. Used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.
3605 Sends trace output to the error log. (If you start SQL Server from the command prompt, the output also appears on the screen.)
3607 Skips automatic recovery (at startup) for all databases.
3608 Skips automatic recovery (at startup) for all databases except the master database.
3609 Skips the creation of the tempdb database at startup. Use this trace flag if the device or devices on which tempdb resides are problematic or problems exist in the model database.
3626 Turns on tracking of the CPU data for the sysprocesses table.
3640 Eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. This is similar to the session setting of SET NOCOUNT ON, but when set as a trace flag, every client session is handled this way.
4022 Bypasses automatically started procedures.
4030 Prints both a byte and ASCII representation of the receive buffer. Used when you want to see what queries a client is sending to SQL Server. You can use this trace flag if you experience a protection violation and want to determine which statement caused it. Typically, you can set this flag globally or use SQL Server Enterprise Manager. You can also use DBCC INPUTBUFFER.
4031 Prints both a byte and ASCII representation of the send buffers (what SQL Server sends back to the client). You can also use DBCC OUTPUTBUFFER.
4032 Traces the SQL commands coming in from the client. The output destination of the trace flag is controlled with the 3605/3604 trace flags.
7300 Retrieves extended information about any error you encounter when you execute a distributed query.
7501 Dynamic cursors are used by default on forward-only cursors. Dynamic cursors are faster than in earlier versions and no longer require unique indexes. This flag disables the dynamic cursor enhancements and reverts to version 6.0 behavior.
7502 Disables the caching of cursor plans for extended stored procedures.
7505 Enables version 6.x handling of return codes when calling dbcursorfetchex and the resulting cursor position follows the end of the cursor result set.
7525 Reverts to the SQL Server 7.0 behavior of closing nonstatic cursors regardless of the SET CURSOR_CLOSE_ON_COMMIT state in SQL Server 2000.
8202 Replicates all UPDATE commands as DELETE/INSERT pairs at the publisher.
8206 Supports stored procedure execution with a user specified owner name for SQL Server subscribers or without owner qualification for heterogeneous subscribers in SQL Server 2000.
8207 Enables singleton updates for Transactional Replication, released with SQL Server 2000 Service Pack 1.
8599 Allows you to use a savepoint within a distributed transaction.
8679 Prevents the SQL Server optimizer from using a Hash Match Team operator.
8687 Used to disable query parallelism.
8721 Dumps information into the error log when AutoStat has been run.
8783 Allows DELETE, INSERT, and UPDATE statements to honor the SET ROWCOUNT ON setting when enabled.
8816 Logs every two-digit year conversion to a four-digit year.

To find out more about any of the Transact-SQL statements, commands, stored procedures or system tables referenced in this article, please download and purchase a copy of Transact-SQL Language Reference Guide from my website www.TransactSQL.Com.

Copyright 2002 by Randy Dyess, All rights Reserved.


Leave a comment

Your email address will not be published.