In this article, I want to tell you what should you know about some undocumented SQL Server 7.0 trace flags, and how you can use them for administration and monitoring.
Trace flags are used to temporarily set specific server characteristics, or to switch on or off a particular behavior. You can set trace flags by using the DBCC TRACEON command or by using the -T option along with the sqlservr command-line executable. After activated, trace flag remain in effect until you restart the server, or until you deactivate the trace flag by using the DBCC TRACEOFF command.
Using Trace Flags
To use the DBCC TRACEON command to turn on a specified trace flag, use this syntax:
DBCC TRACEON (trace# [,…n])
To use the DBCC TRACEON command to turn off a specified trace flag, use this syntax:
DBCC TRACEOFF (trace# [,…n])
You can also use the DBCC TRACESTATUS command to find out which trace flags are currently turned on in your server using this syntax:
DBCC TRACESTATUS (trace# [,…n])
For example, to get the status information for all trace flags currently turned on, run this code:
DBCC TRACESTATUS(-1)
1. Trace flag -1 (undocumented)
This trace flag sets trace flags for all client connections, rather than for a single client connection. Is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF. This trace flag was documented in SQL Server 6.5 Books Online, but was not documented in SQL Server 7.0.
2. Trace flag 1807 (undocumented)
You cannot create a database file on a mapped or UNC network location under SQL Server 7.0. You can get around this by turning on trace flag 1807.
3. Trace flag 2521 (undocumented)
Trace flag 2521 is used to facilitate capturing the Sqlservr.exe user-mode crash dump for postmortem analysis. Note: This trace flag works only on SQL Server 7.0 with service pack 2 or later.
4. Trace flag 3604 (documented, but mentioned here because of how useful it is)
Trace flag 3604 sends trace output to the client. This trace flag is used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.