Usage of Trace tag

Enable the specified trace tag.
DBCC traceon (trace # [,... n] [,-1]) [with no_infomsgs]

Disable the specified trace tag.
DBCC traceoff (trace # [,... n] [,-1]) [with no_infomsgs]

Displays the status of the trace flag.
DBCC tracestatus ([[trace # [,... n] [,] [-1]) [with no_infomsgs]

The trace flag is used to temporarily set the characteristics of a specific server or disable a specific behavior. For example, if the trace flag 3205 is set when an instance of SQL Server is started, hardware compression of the tape drive is disabled. Tracking marks are often used to diagnose performance problems, or to debug stored procedures or complex computer systems.

The following table lists and describes the trace flags available in SQL Server.

Note:
Future versions of SQL Server may not support tracking behavior.

Tracking flag	Description
260	Print version control information about the dynamic link library (DLL) of the extended stored procedure. Related_ Getxpversion ()For more information, see create an extended storage process. Scope: Global or session
1204	Return the resources and types of the locks involved in the deadlock and the affected current commands. Scope: only global
1211	Disable lock upgrade based on insufficient memory or number of locks. The SQL Server database engine does not upgrade row or page locks to table locks. This trace flag can generate an excessive number of locks. This will reduce the performance of the database engine or cause a 1204 error due to insufficient memory (unable to allocate lock resources ). For more information, see lock upgrade (database engine ). If trace flags 1211 and 1224 are set at the same time, 1211 takes precedence over 1224. However, we recommend that you use 1211 because the trail mark 1224 is not upgraded in all cases (even when memory is insufficient. This helps avoid the "insufficient lock" error when multiple locks are used. Scope: Global or session
1222	Return the resources and types of the locks involved in deadlocks and the affected current commands in XML format that does not conform to any XSD architecture. Scope: only global
1224	Disable lock upgrade based on the number of locks. However, if the memory is insufficient, the lock upgrade can still be activated. If the volume of memory used by the Lock Object exceeds one of the following conditions, the database engine upgrades the row lock or page lock to the table (or partition) Lock: The memory used by the database engine is 40% of the memory, except for the memory allocation of the address window extension plug-in (AWE. OnlySp_configureOfLocksThis applies only when the parameter is set to 0. UseSp_configureOfLocks40% of the lock memory configured by the parameter. For more information, see set server configuration options. If trace flags 1211 and 1224 are set at the same time, 1211 takes precedence over 1224. However, we recommend that you use 1211 because the trail mark 1224 is not upgraded in all cases (even when memory is insufficient. This helps avoid the "insufficient lock" error when multiple locks are used. Note:You can also use the lock_escalation option of the alter table statement to control lock upgrades at the table or HOBT level. Scope: Global or session
2528	Disable Parallel Object check performed by DBCC checkdb, DBCC checkfilegroup, and DBCC checktable. By default, the degree of parallelism is automatically determined by the query processor. The configuration of the maximum degree of parallelism is the same as that of the parallel query. For more information, see the max degree of parallelism options. In general, parallel DBCC should be enabled. For DBCC checkdb, the query processor recalculates the value and automatically adjusts the degree of parallelism for each table or batch of tables. Sometimes the check may start when the server is almost idle. If the Administrator knows that the load will increase before the check is complete, you may need to manually reduce the degree of parallelism or disable the degree of parallelism. Disabling the parallel check on DBCC may lead to a longer completion time of DBCC. If the tablock function is enabled and the degree of parallelism is disabled when running DBCC, the table may be locked for a longer time. Scope: Global or session
3205	By default, if the tape drive supports hardware compression, the dump or backup statement uses this function. This tracking flag can be used to disable hardware compression for the tape drive. This option is useful when you need to swap tape with other sites or tape drives that do not support compression. Scope: Global or session
3226	By default, each successful backup operation adds an entry to the SQL Server Error Log and System Event Log. If log backups are created frequently, these successful messages will be quickly accumulated, resulting in a huge error log, making it very difficult to find other messages. With this trace flag, you can cancel these log entries. This method is useful if you frequently run log backup without any scripts dependent on these entries.
3608	Disable Automatic startup and recovery of SQL ServerMasterAny database other than the database. The database will be started and restored when you access the database. You may not be able to run certain functions, such as Snapshot isolation and read and submit snapshots. It is used for mobile system databases and mobile user databases. Do not use it in normal operation.
3625	Limit the amount of information returned in an error message. For more information, see metadata visibility configuration. Scope: only global
4199	Control previous changes to multiple query Optimizers Based on Multiple trace flags. For more information, see this Microsoft support article. Scope: Global or session
4616	Enable the application role to view the server-level metadata. In SQL Server, application roles cannot access metadata other than their own databases, because application roles are not associated with server-level subjects. This is a change to the behavior of earlier versions of SQL Server. Setting this global flag disables new restrictions and allows application roles to access server-level metadata. Scope: only global
6527	It is prohibited to generate a memory dump when there is an exception of insufficient memory for the first time in CLR integration. By default, SQL Server generates a small memory dump when the first memory insufficiency exception occurs in CLR. The action of the trail flag is as follows: If used as a startup tracking flag, a memory dump will never be generated. However, if other trace flags are used, a memory dump may be generated. If this trace flag is enabled on a running server, the memory dump is not automatically generated from this time on. However, if a memory dump has been generated due to a memory insufficiency exception in the CLR, this trace flag will have no effect. Scope: only global
7806	Enable the dedicated administrator connection (DAC) on SQL Server Express ). By default, DAC resources are not retained on SQL Server Express. For more information, see connect with a dedicated administrator. Scope: only global