SQL Server trace flags provide additional information about SQL Server operations or change certain behaviors, usually for backward compatibility. In general, trace flags should be used to temporarily work around a problem until a permanent solution is put in place. Although the information provided by trace flags can help you diagnose problems, keep in mind that trace flags are not part of the supported feature set. This means that future compatibility or continued use is not assured. In addition, your primary support provider, including Microsoft, will usually not have further information and will not answer questions regarding the trace flags or their output. In other words, the information provided in this section is to be used "as is."
Caution It is generally recommended that you use trace flags only when directed by your primary support provider or by this chapter. Beware against experimenting with trace flags not documented here. They can cause unexpected and potentially serious problems.
Trace Flags are used to temporarily set a specific server characteristic. Trace flags can be activated through three methods: by using the DBCC statement, with the -T option with the sqlservr command-line executable, or in setup. Once set, and trace flags are in effect only until reset (for options set with the DBCC statement) or until the server is stopped and restarted without the trace flag. For details, see the Trace Flags topic, the Utilities topic, and the DBCC statement in the Microsoft SQL Server Transact-SQL Reference.
These are the trace flags available in SQL Server 6.0. Trace flags noted with an asterisk (*) are for information only. Others are used to change program behavior.
| 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 in the Microsoft SQL Server Transact-SQL Reference. |
| 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 in the Microsoft SQL Server Transact-SQL Reference. |
| 204 | Generally, allows non-ANSI-standard behavior for various features for backward compatibility with earlier version of SQL Server: 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 in the Microsoft SQL Server Transact-SQL Reference. |
| 206 | Provides backward compatibility for the SETUSER statement. For details, see the SETUSER statement. |
| 242 | Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired. |
| 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 (for example, 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. 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 ON, as described in the SET statement. |
| 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 detailed information about the commands 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 in the Microsoft SQL Server Transact-SQL Reference. |
| 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 (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. |
| 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. 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. |
Unlike the other trace flags, the trace flag - 1 activates tracing only in processes that were open when the DBCC TRACEON ( - 1) statement was issued. Because new connections are not traced, you should periodically set the - 1 trace flag using DBCC TRACEON ( - 1) if you want to pick up new connections.
Setting trace flags using DBCC TRACEON ( - 1) differs from using the -T command-line option (as described in "Starting SQL Server with Trace Flags," later in this chapter):