Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

Trace Flags

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. For example, if trace flag 3205 is set when Microsoft® SQL Server™ starts, hardware compression for tape drivers is disabled. Trace flags are often used to diagnose performance issues or to debug stored procedures or complex computer systems.

These trace flags are available in SQL Server.

Note  Trace flag behaviors may or may not be supported in future releases.

Trace flag Description
260 Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(), see Creating Extended Stored Procedures.
1204 Returns the type of locks participating in the deadlock and the current command affected. The deadlock information is automatically sent to the error log.
2528 Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. By default, the degree of parallelism is determined automatically by the query processor.  The maximum degree of parallelism is configured in the same manner as that of parallel queries. For more information, see max degree of parallelism Option.

Parallel DBCC should typically be left enabled. In the case of DBCC CHECKDB, the query processor re-evaluates and automatically adjusts parallelism with each table or batch of tables checked. In some cases, checking may commence while the server is virtually idle. An administrator who knows that the load will increase before checking is complete may want to manually decrease or disable parallelism.

However, disabling parallel checking can cause a decrease in overall database performance. Decreasing the degree of parallelism increases the amount of transaction log that must be scanned. This in turn increases the demand for tempdb space and results in a non-linear increase in the time required for dbcc to complete its checks. If DBCC is run with the TABLOCK feature enabled and parallelism turned off, tables may be locked for longer periods of time.

3205 By default, if a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression.

Examples
A. Set trace flags using DBCC TRACEON

This example turns on trace flag 3205 by using DBCC TRACEON.

DBCC TRACEON (3205)
B. Set trace flags at the command prompt

This example turns on trace flag 3205 at the command prompt.

sqlservr –d"C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf" –T3205

See Also

Data Types

DBCC INPUTBUFFER

DBCC OUTPUTBUFFER

DBCC TRACEOFF

DBCC TRACEON

EXECUTE

SELECT

SET NOCOUNT

sp_dboption

SQL Server Backward Compatibility Details

sqlservr Application

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft