You can use DBCC TRACESTATUS command to get
the status information for the
particular trace flag(s) currently turned on. This
is useful to understand the state of your server. 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#. I use this at most of the places to get
the server settings.
Just to illustrate here
is an example of the syntax used:
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])
So there are many
documented trace flags used in SQL Server Books Online documented. But we will
get a couple of steps further and watch couple of un-documented trace flags
also. Remember this is NOT the exhaustive list. But just a useful set.
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.
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.
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.
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.
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.
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.
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.
If turns on, then automatically started procedures
will be bypassed. This trace flag
described in CREATE PROCEDURE statement in the SQL
Server Books Online.
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.