Possible SQL Server Trace Flag Gotcha


I was trying to turn on Traceflag 3226 in order to suppress the swarm of successful transaction log backup messages that floods the SQL Server (Error) Logs (& Windows Application logs). After all, if you’ve got more than a few databases, and are running regular frequent transaction log backups due to a tight recovery point objective (RPO), you’ll see a lot of these messages getting in the way of more useful and important information.

To see which trace flags are currently active, use the DBCC TRACEFLAG statement:

Oh, look. We’ve got one already. Googling tells me that this is the flag that’s enabled by default by Microsoft to provide useful deadlock information (an XML graph) rather than just reporting the fact that a deadlock has occurred.

The way to add a new global “permanent” traceflag is with SQL Server Configuration Manager. Go into the SQL Server properties, and head to the startup parameters page.

Add the trace flag by typing “-T3226” into the “Specify a startup parameter” box and clicking the “Add” button. Unfortunately, you’ll now need to restart SQL Server to pick up this change.

And now check your work was successful.

Hang on. What happened to the trace flag 1222, so kindly provided by MS? Good question. It looks as though you lose any MS-provided defaults if you specify your own additional traceflags. Back to configuration manager to add the other flag:

Re-restart, and re-check:

NB:T1222 seems to be enabled by default on the SQL Servers (SQL Server 2014) I’ve built that have either MSDN or “real” licences, but only sporadically enabled on machines running evaluation / developer editions. Which is a bit odd, as the installations done on those latter machines was all run through the same script and configuration file…

MSDN Reference links: