Trace flags are used to temporarily set specific server characteristics. For example, if you set the trace flag 8501 when you start SQL Server, detailed information is logged about MS DTC transactions.
Trace flags are generally used to switch off a particular behavior. SQL Server 6.5 introduces many new features, some of which can be switched off by using a trace flag. Often trace flags are used to diagnose performance issues and to debug stored procedures or complex systems.
This is how to start a trace flag by using the DBCC command:
DBCC TRACEON (8501)
These flags are new or enhanced in SQL Server 6.5.
| Flag | Description |
|---|---|
| 110 | Disables ANSI SELECT characteristics. For more information, see SELECT Statement. |
| 204 | Supports queries that contain sort columns in the ORDER BY clause that are not in the select list when the DISTINCT keyword is supplied. For more information, see the Microsoft SQL Server Transact-SQL Reference. |
| 237 | Disables the requirement for REFERENCES permission to create a foreign key on a table that is not owned by the key creator, and when disabled ensures ANSI-standard behavior. When enabled, this flag needs only SELECT permission and SQL Server 6.0 behavior is ensured. |
| 246 | Disables error reporting when no column name is given to a column created by a SELECT INTO or a CREATE VIEW statement. |
| 330 | Enables full output when using the SET SHOWPLAN option, which gives detailed information about joins. |
| 506 | Enforces ANSI standards regarding null values for comparisons between variables and/or parameters. Any comparison of variables and/or parameters that contain a NULL will always result in a NULL. This flag is disabled if ANSI_NULLS is enabled (set to True). |
| 1081 | Sets a flag in index pages that prevents index pages from being removed from the cache the first time the cache tries to remove them to make space for new data. Unless the index page is reused and the flag is reset, the index page is received next time the cache requires space for new data pages. This flag extends the life of the index page within the cache. Use this flag on databases where performance gained by retaining the index pages in the cache outweighs the performance lost by the increase in data page I/O. |
| 2701 | Sets the global variable @@ERROR to 50000 for RAISERROR messages with severity levels of 10 and under. When disabled, sets the global variable @@ERROR to 0 for RAISERROR messages with severity levels of 10 and under. |
| 3002 | Backups will have SQL Server 6.0 DUMP format. To use this option the database must be running in stand-alone mode by setting the database option "DBO UseOnly" to TRUE. |
| 4022 | In SQL Server 6.5, bypasses automatically started procedures. |
| 7501 | In SQL Server 6.5, dynamic cursors are used by default on forward-only cursors. Dynamic cursors are faster in version 6.5 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. |
| 8501 | Performs detailed logging that describes all MS DTC-related context and state changes. |
Trace flags are set when SQL Server is started at the command prompt. For more information about "sqlservr command-line executable," see the Microsoft SQL Server Transact-SQL Reference. This is how to start SQL Server with a trace flag at the command prompt:
sqlservr /dc:\mssql\data\master.dat /T8501