SQL SERVER – What is DBCC TRACEON and DBCC TRACEOFF messages in ERRORLOG?

One of my clients asked me more about these messages logged in the SQL Server ERRORLOG. If you are new to SQL Server and don’t know how to reach to ERRORLOG, read below Where is ERRORLOG? Various Ways to Find ERRORLOG Location. In this blog post we will discuss about DBCC TRACEON.

Here is the message in ERRORLOG

DBCC TRACEON 1222, server process ID (SPID) 53. This is an informational message only; no user action is required

I explained that this message means that SPID 53 has turned on the trace flag 1222.

DBCC TRACEOFF (1222,-1)

Trace flags are used in SQL Server to change the behavior of certain areas. For example, 1222 trace flag is used to enable logging of deadlocks in SQL Server ERRORLOG file. You can imagine this like an if condition in SQL Server.

If trace flag is ON
{Do this also}

Similarly, you can also see message below.

DBCC TRACEOFF 1222, server process ID (SPID) 57. This is an informational message only; no user action is required

Above message means trace flag was turned off by SPID 57.

DBCC TRACEOFF (1222,-1)

Interestingly, ERRORLOG doesn’t tell us which user has done that. To find that out, we can use SSMS standard reports, Configuration Changes History. SSMS: Configuration Changes History

There is a trace flag available with SQL Server to suppress these messages. Help : How to suppress DBCC TRACEON/OFF messages in the Error Log?

Even if 2505 is enabled, we can still get the details from “Configuration Changes History” because as I explained in that blog that this information is picked from default trace not ERRORLOG.

SQL SERVER - What is DBCC TRACEON and DBCC TRACEOFF messages in ERRORLOG? traceon-01

ERRORLOG shows only the last message because we have enabled 2505 after that.

SQL SERVER - What is DBCC TRACEON and DBCC TRACEOFF messages in ERRORLOG? traceon-02

This information was enough for my client to get more details. Have you used any such trace flag in your production environment?

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Log, SQL Server, SQL Server DBCC
Previous Post
SQL SERVER – How to Change SQL Server Collation on Microsoft Azure VM (IaaS)
Next Post
SQL SERVER – Huge Transaction Log in Snapshot Replication!

Related Posts

Leave a Reply