Home Library Learn Downloads Support Community
Sign in | United States - English | |

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 Procedures1.
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 Option2.

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 Types3

DBCC INPUTBUFFER4

DBCC OUTPUTBUFFER5

DBCC TRACEOFF6

DBCC TRACEON7

EXECUTE8

SELECT9

SET NOCOUNT10

sp_dboption11

SQL Server Backward Compatibility Details12

sqlservr Application13

Links Table
1http://msdn.microsoft.com/en-us/library/aa215790(v=sql.80).aspx
2http://msdn.microsoft.com/en-us/library/aa196725(v=sql.80).aspx
3http://msdn.microsoft.com/en-us/library/aa258271(v=sql.80).aspx
4http://msdn.microsoft.com/en-us/library/aa258826(v=sql.80).aspx
5http://msdn.microsoft.com/en-us/library/aa258810(v=sql.80).aspx
6http://msdn.microsoft.com/en-us/library/aa258288(v=sql.80).aspx
7http://msdn.microsoft.com/en-us/library/aa258823(v=sql.80).aspx
8http://msdn.microsoft.com/en-us/library/aa258848(v=sql.80).aspx
9http://msdn.microsoft.com/en-us/library/aa259187(v=sql.80).aspx
10http://msdn.microsoft.com/en-us/library/aa259204(v=sql.80).aspx
11http://msdn.microsoft.com/en-us/library/aa933268(v=sql.80).aspx
12http://msdn.microsoft.com/en-us/library/aa197112(v=sql.80).aspx
13http://msdn.microsoft.com/en-us/library/aa214023(v=sql.80).aspx
Community Content Add
Annotations FAQ