Trace Flags
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. |
| 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