articles >> performance tuning >> Some Useful Undocumented SQL Server 2000 Trace ...

Some Useful Undocumented SQL Server 2000 Trace Flags

By : Alexander Chigrik Mar 31, 2004

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.

 

Trace flag 3608

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 is not documented in the SQL Server 7.0 and SQL Server 2000 Books Online.

 

Trace flag 8202

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.

 

Published with the express written permission of the author. Copyright 2002.