Trace Flags

For SQL Server 6.5 information, see Trace Flags in What's New for SQL Server 6.5.

Trace Flags are used to temporarily set a specific server characteristic. Trace flags can be activated through two methods: by using the DBCC statement and with the -T option with the sqlservr command-line executable. When set, trace flags are in effect only until reset (for options set with the DBCC statement) or until the server is stopped and restarted. For details, see the Utilities topic and the DBCC statement. For details on troubleshooting and setting and using trace flags, see the Microsoft SQL Server Administrator's Companion.

The following table summarizes the trace flags available in SQL Server 6.0. These trace flags are provided primarily for backward compatibility. In general, trace flags should be used to temporarily work around a problem until a permanent solution is put in place. Trace flags are not a part of the supported feature set, and future compatibility or continued use is not assured.

These are the trace flags available in SQL Server 6.0:



Trace flag	Description
- 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 (with sqlservr) automatically apply to all connections, this trace flag is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF. For details, see the sqlservr command-line executable and DBCC statement.
106	Disables line number information for syntax errors.
107	Interprets numbers with a decimal point as float instead of decimal. For details, see the Datatypes topic.
204	Generally, allows non-ANSI-standard behavior for various features: · Allows queries that contain aggregates or a GROUP BY clause to have items in the select list that are not in the GROUP BY clause and are not aggregate functions. ANSI-standard SQL does not allow this. · Ignores trailing blanks in the LIKE pattern. For details, see the Search Conditions and Wildcard Characters topics.
206	Provides backward compatibility for the SETUSER statement. For details, see the SETUSER statement.
243	Provides backward compatibility for nullability behavior. When set, SQL Server has the same nullability violation behavior as that of a 4.2 server: · Processing of the entire batch will be terminated if the nullability error (inserting NULL into a NOT NULL field) can be detected at compile time · Processing of the offending row will be skipped but the command will continue if the nullability violation is detected at run time The behavior of SQL Server 6.0 is now more consistent ¾ all nullability checks are made at run time and a nullability violation results in the command terminating but the batch/transaction continuing to process.
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 initially violates a constraint, followed by another 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 statements based on a SELECT, 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.
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. Index selection information is also available in a more readable format using SET SHOWPLAN ON, as described with the SET statement. Trace flag 302 should be used with trace flag 310 to show the actual join ordering.
310	Prints information about join order.
325	Prints information about the cost of using a nonclustered index or a sort to process an ORDER BY clause.
326	Prints information about the estimated and actual cost of sorts.
652	Disables read ahead for the server.
653	Disables read ahead for the current connection.
1200	Prints lock information (the process ID and type of lock requested).
1204	Returns the type of locks participating in the deadlock and the current command affected.
1205	Returns more detailed information on the command being executed at the time of a deadlock.
1609	Turns on the unpacking and checking of RPC information with the execution of the sp_sqlexec extended stored procedure in Open Data Services. Use this flag only when applications depend on the old behavior. The application should be changed to use EXECUTE sql_string rather than sp_sqlexec. For details, see the EXECUTE statement.
1704	Prints information when a temporary table is created or dropped.
3502	Prints a message to the log at the start and end of each checkpoint.
3205	By default, if a tape drive supports hardware compression, the DUMP statement will use it. With this trace flag, you can disable hardware compression for tape drives. This might be useful when you want to exchange tapes with other sites or tape drives that do not support compression.
3503	Indicates whether the checkpoint at the end of automatic recovery was skipped for a database (this applies only to READ ONLY databases). For details, see the sp_dboption system stored procedure.
3604	Sends trace output to the client. ( ) This trace flag is 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 line, the output will also appear on the screen.)
3607	Skips automatic recovery for all databases.
3608	Skips automatic recovery for all databases except the master database.
3609	Skips the creation of the tempdb database. Use this trace flag if the device or devices on which tempdb resides are problematic or problems exist in the model database.
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 NOCOUNT, but when set as a trace flag every client session is handled this way. For details, see the SET statement.
4022	Skips the execution of all startup stored procedures. For more information, see the CREATE PROCEDURE statement and the sp_makestartup system stored procedure.
4030	Prints both a byte and ASCII representation of the receive buffer. This trace flag is usually used when you need to see what queries a client is sending to SQL Server. You might use this trace flag if you experience a protection violation and need to determine which statement caused it. Typically, you would set this flag globally or use SQL Enterprise Manager. You can also use DBCC INPUTBUFFER. For details, see the DBCC statement.
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. For details, see the DBCC statement.
4032	Prints only an ASCII representation of the receive buffer. Usually used in place of trace flag 4030 (if trace output speed is important) when you need to see what queries a client is sending to SQL Server.

Trace Flags

See also