SQL Server 7.0: Some useful trace flags

Introduction

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.

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.

5. Trace flag 3608 (undocumented)

This trace flag skips automatic recovery (at startup) for all databases except the master database. Trace flag 3608 was documented in the SQL Server 6.5 Books Online, but was not documented in SQL Server 7.0.

6. Trace flag 3222 (undocumented) If you drop, create, or rebuild indexes at the time of a database or transaction log backup, then subsequent attempts to load the backup may fail. This is because the recovery process can read ahead to increase the speed of recovery while rolling forward index maintenance activities. Trace flag 3222 disables the read ahead that is used by the recovery operation during roll forward operations.

7. Trace flag 8202 (undocumented)

This trace flag is used to replicate UPDATE as a DELETE/INSERT pair during replication. UPDATE commands at the publisher can be run as an "on-page DELETE/INSERT" or a "full DELETE/INSERT". If the UPDATE command is run as an "on-page DELETE/INSERT," the Log Reader Agent sends the UPDATE command to the subscriber. If the UPDATE command is run as a "full DELETE/INSERT," the Log Reader Agent sends the UPDATE as a DELETE/INSERT pair. If you turn on trace flag 8202, then UPDATE commands at the publisher will be always sent to the subscriber as a DELETE/INSERT pair.

8. Trace flag 8816 (undocumented)

SQL Server can convert a two-digit year to a four-digit year, but Microsoft recommends using the full four-digit year for all date operations. This trace flag logs every two-digit year conversion to a four-digit year. It can be useful to find year 2000 bugs in older programs.

See this article about SQL Server 2000 trace flags:
SQL Server 2000: Some useful trace flags