SQL Server 2000 DBCC Traceon flags

SQL Server 2000: Some useful trace flags

SQL Server 2000: Some useful trace flags

Alexander Chigrik
chigrik@mssqlcity.com

---

Introduction

In this article, I want to tell you, what should you know about trace flags, and how you can use some useful trace flags in SQL Server 2000 for administering and monitoring.

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. You can set trace flags by using DBCC TRACEON command or by using the -T option with the sqlservr command- line executable. After activated, trace flag will be in effect until you restart server, or until you deactivate trace flag by using DBCC TRACEOFF command.

Trace flags

You can use DBCC TRACESTATUS command to get the status information for the particular trace flag(s) currently turned on. This is the syntax from SQL Server Books Online:

DBCC TRACESTATUS (trace# [,...n])

To get the status information for all trace flags currently turned on, you can use -1 for trace#.

This is the example:

DBCC TRACESTATUS(-1)

You can use DBCC TRACEON command to turn on the specified trace flag. This is the syntax from SQL Server Books Online:

DBCC TRACEON (trace# [,...n])

If you want to turn off the specified trace flag(s), you can use DBCC TRACEOFF command.
This is the syntax from SQL Server Books Online:

DBCC TRACEOFF (trace# [,...n])

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. The setting of the Trace flag -1 is not visible with DBCC TRACESTATUS command, but work without problems.
This trace flag was documented in SQL Server 6.5 Books Online, but was not documented in SQL Server 7.0 and SQL Server 2000.

2. Trace flag 1204 (undocumented).

This trace flag returns the type of locks participating in the deadlock and the current command affected. This trace flag was documented in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

3. Trace flag 1205 (undocumented).

This trace flag returns more detailed information about the command being executed at the time of a deadlock. This trace flag was documented in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

4. Trace flag 1807 (undocumented).

You cannot create a database file on a mapped or UNC network location. This opportunity is generally unsupported under SQL Server 7.0 and SQL Server 2000. You can bypass this by turn on trace flag 1807.

5. Trace flag 3604 (undocumented).

One of the most used trace flag. 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 SQL Server 6.5 Books Online and in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

6. Trace flag 3605 (undocumented).

In comparison with Trace flag 3604, this trace flag sends trace output to the error log. Trace flag 3605 was documented in SQL Server 6.5 Books Online and in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

7. 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 SQL Server 6.5 Books Online, but was not documented in SQL Server 7.0 and SQL Server 2000.

8. Trace flag 4022.

If turns on, then automatically started procedures will be bypassed. This trace flag described in CREATE PROCEDURE statement in the SQL Server Books Online.

9. Trace flag 8202 (undocumented).

This trace flag used to replicate UPDATE as DELETE/INSERT pair. Let me to describe.
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 Logreader send UDPATE command to the subscriber, If the UPDATE command is run as a "full DELETE/INSERT," the Logreader send UPDATE as DELETE/INSERT Pair. If you turn on trace flag 8202, then UPDATE commands at the publisher will be always send to the subscriber as DELETE/INSERT pair.

Literature

1. SQL Server Books Online

2. INF: Trace Flag to Replicate UPDATE as DELETE/INSERT Pair