In this article, I want to tell you what should you know about some undocumented SQL Server 2000 trace flags, and how you can use them for administration and monitoring of SQL Server 2000.
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, a trace flag remains 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)
Trace flag -1
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 is not documented in SQL Server 7.0 and SQL Server 2000.
Trace flag 1204
This trace flag returns the type of locks participating in a deadlock and the current command affected. This trace flag was documented in the SQL Server 7.0 Books Online, but not documented in the SQL Server 2000 Books Online.
Trace flag 1205
This trace flag returns more detailed information about the command being executed at the time of a deadlock. This trace flag was documented in the SQL Server 7.0 Books Online, but not documented in SQL Server 2000 Books Online.
Trace flag 1807
By default, you cannot create a database file on a mapped or UNC network location under SQL Server 2000. You can get around this by turning on trace flag 1807.
Trace flag 3604
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. Trace flag 3604 was documented in the SQL Server 6.5 Books Online and in the SQL Server 7.0 Books Online, but is not documented in the SQL Server 2000 Books Online.