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:

DBCC TraceStatus 1

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.

SQL Server Properties

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.

DBCC TraceStatus 2

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:

SQL Server Properties 2

Re-restart, and re-check:

DBCC TraceStatus 3

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:

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , . Bookmark the permalink.

One Response to Possible SQL Server Trace Flag Gotcha

  1. thomasrushton says:

    I have since found a PowerShell script-based solution that I shall add to my automated build scripts. http://www.erichumphrey.com/2011/03/change-sql-startup-parameters-with-powershell/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s