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.
]]>