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 |
|---|---|
| 106 | Disables line number information for syntax errors. |
| 107 | Interprets numbers with a decimal point as float instead of decimal. |
| 206 | Provides backward compatibility for the SETUSER statement. For more information, see SETUSER. |
| 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 | Provides backward compatibility for nullability behavior. When set, SQL Server has the same nullability violation behavior as that of a version 4.2 server:
· Processing of the entire batch is terminated if the nullability error (inserting NULL into a NOT NULL field) can be detected at compile time. · Processing of the offending row is skipped, but the command continues if the nullability violation is detected at run time. 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 statements based on a SELECT, and multirow UPDATE statements. This checking requires more work tables. With this trace flag you can disallow interim constraint violations, thus requiring fewer work tables. |
| 260 | Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(), see Creating Extended Stored Procedures. |
| 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. |
| 330 | Enables full output when using the SET SHOWPLAN 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.
This flag is disabled if ANSI_NULLS is enabled with a setting of TRUE. |
| 1204 | Returns the type of locks participating in the deadlock and the current command affected. |
| 1205 | Returns more detailed information about the command being executed at the time of a deadlock. |
| 1609 | Turns on the unpacking and checking of remote procedure call (RPC) information with the execution of sp_sqlexec in Open Data Services. Used only when applications depend on the old behavior. The application should be changed to EXECUTE sql_string rather than sp_sqlexec. |
| 1704 | Prints information when a temporary table is created or dropped. |
| 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. |
| 3205 | By default, if a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression. |
| 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.) |
| 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, 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 | 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 want to see what queries a client is sending to SQL Server. |
| 7501 | Available only in Microsoft SQL Server 6.5 and earlier versions. 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 | Available only in Microsoft SQL Server 6.5 and earlier versions. 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. |
| 8783 | Allows DELETE, INSERT, and UPDATE statements to honor the SET ROWCOUNT ON setting when enabled. |
This example turns on trace flag 8783 by using DBCC TRACEON.
DBCC TRACEON (8783)
This examples turns on trace flag 8783 at the command prompt.
sqlservr /dc:\mssql7\data\master.dat /T8783
| Data Types | SELECT |
| DBCC INPUTBUFFER | SET NOCOUNT |
| DBCC OUTPUTBUFFER | sp_dboption |
| DBCC TRACEOFF | SQL Server Backward Compatibility Details |
| DBCC TRACEON | sqlservr Application |
| EXECUTE |