Trace Flags
Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. For example, if trace flag 3205 is set when Microsoft® SQL Server™ starts, hardware compression for tape drivers is disabled. Trace flags are often used to diagnose performance issues or to debug stored procedures or complex computer systems.
These trace flags are available in SQL Server.
Note Trace flag behaviors may or may not be supported in future releases.
Trace Flags
Trace flags are used to temporarily set specific server characteristics or to
switch off a particular behavior. For example, if trace flag 8783 is set when
Microsoft SQL Server starts, SQL Server honors the SET ROWCOUNT ON statement
for all DELETE, INSERT, and UPDATE statements. Trace flags are often used to
diagnose performance issues or to debug stored procedures or complex computer
systems.
These trace flags are available in Microsoft SQL Server.
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 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. |
| 208 | SET QUOTED IDENTIFIER ON. |
| 237 | Disables the requirement for REFERENCES permissions to create a foreign key on a table that is not owned by the key creator, and, when disabled, ensures SQL- 92 standard behavior. When enabled, this flag needs only SELECT permissions to ensure SQL Server behavior. |
| 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. |
Examples
A. Set trace flags using DBCC TRACEON
This example turns on trace flag 3205 by using DBCC TRACEON.
DBCC TRACEON (3205)
B. Set trace flags at the command prompt
This example turns on trace flag 3205 at the command prompt.
sqlservr –d"C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf" –T3205
DBCC TRACESTATUS
Displays the status of trace flags.
Syntax
DBCC TRACESTATUS ( trace# [ ,...n ] )
Arguments
trace#
Is the number of the trace flag whose status will be displayed.
n
Is a placeholder that indicates multiple trace flags can be specified.
Result Sets
DBCC TRACESTATUS returns a column for the trace flag number and a column for
the status, indicating whether the trace flag is ON (1) or OFF (0). To get status
information for all trace flags currently turned on, specify - 1 for trace#.
Remarks
To enable certain trace flags, use DBCC TRACEON. To disable trace flags, use
DBCC TRACEOFF.
Permissions
DBCC TRACESTATUS permissions default to any user.
Examples
A. Display the status of all trace flags currently enabled
This example displays the status of all currently enabled trace flags by specifying
a value of -1.
DBCC TRACESTATUS(-1)
GO
B. Display the status of multiple trace flags
This example displays the status of trace flags 2528 and 3205.
DBCC TRACESTATUS (2528, 3205)
GO