SQL Server Health Check

Trace Flags in SQL Server

Updated October 29, 20253 min read

Trace flags in SQL Server, explained. A tilted book on a bookshelf reveals a hidden lever.

What are trace flags in SQL Server?

SQL Server trace flags are configuration handles that can enable or disable specific SQL Server characteristics or change a particular SQL Server behavior.

Trace flags turn on/off certain hidden SQL Server features that have no user interface components.

Trace flags may also be recommended by Microsoft Support to address behavior that is negatively impacting a specific workload.

It is possible to make SQL Server perform better by enabling or disabling some of them.

How to check for enabled trace flags?

The command below will show you a table with all active traces.

T-SQL
DBCC TRACESTATUS
Figure 1 - DBCC TRACESTATUS output
Figure 1 - DBCC TRACESTATUS output

How do I enable trace flags in SQL Server?

There are two ways to enable or disable them:

Using T-SQL, with the DBCC TRACE command, you have the option to set the trace flag at the session or global level.

Usually, you enable the trace flag to the entire instance (global level). For testing purposes, you may just leave it at the session level.

For example, to enable trace flag 1118 globally, you would run:

T-SQL
DBCC TRACEON (
		1118
		,- 1
		);

To disable it, run:

T-SQL
DBCC TRACEOFF (
		1118
		,- 1
		);

The use of parameter -1 turns on the flag globally.

Note: The advantage of using DBCC TRACEON and DBCC TRACEOFF is that you don’t have to restart the instance to use the trace flag. The drawback is that it will not persist through a restart.

At a minimum consider using these:

  • TF3226 – This stops successful DB backup from the SQL Server error log.
  • TF1221 – Capture deadlock info to the SQL Server error log.
  • TF2389 – Manage increasing key statistics better.
  • TF2390 – Manage unknown key statistics better.
  • TF2371 – Change how AUTO_UPDATE_STATISTICS starts for large tables. Good for VLDBs. (for versions before SQL Server 2016).
  • TF1118 – Provides benefits for customers that make heavy use of the tempdb (for versions before SQL Server 2016).
  • TF3023 – Validate checksums during a backup, and generate a checksum for the entire backup. (for versions before SQL Server 2014)

Trace flags can be powerful for fine-tuning behavior, but they are only one part of a broader monitoring strategy. If your SQL Server alerting system is noisy or misconfigured, critical signals may still get lost.

We’ve written a detailed guide on SQL Server Monitoring & Alerts that explains how to reduce false positives and design alerting that focuses on what truly matters for uptime and performance.

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜