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.
ERRORLOG shows only the last message because we have enabled 2505 after that.
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)
