Trace flags are SQL Servers version of magic spells. They’re kinda mystical with strange numerical names, and they can often do very powerful things. Sometimes good things that eventually become default features (e.g avoiding mixed extents), or when used incorrectly, trace flags can also make your instance very sad.
For this reason there are folks who avoid using trace flags altogether, but regardless of your comfort level I’d suggest there is one trace flag that should be enabled on every single one of your SQL Server instances. It is the glorious trace flag 3226.
WHY YOU NEED IT
Now, what trace flag 3226 does is fairly benign; it simply suppresses entries of successful backup messages from your SQL Server log. While that might not sound like much, it can be a huge benefit to your future self during an emergency.
There will come a day – probably many days over the course of your illustrious career – where you will have to troubleshoot an error. Could be a deadlock, could be a connection error, or it could be a really big problem where minutes or seconds matter.
One of the first places you’ll go to troubleshoot is the SQL Server log, probably through SQL Server Management Studio under the “Management” folder. And when you click on it to open it you’re going see something like this.
Depending on how many databases you have and how frequently you back up database and log files, you could see thousands and thousands of records like this, and you’ll be scrolling through them (or worse, waiting for them to load) and you’ll be shouting in your most colorful speech “Where’s the (something something) error message?!?”
Future you doesn’t want to read about successful log backups. If you did, you could query a table like backupset in msdb and find that info out.
Future you wants to find that error as soon as possible, and that’s why you’re going to go ahead and enable trace flag 3226 today.
HOW YOU ENABLE IT
First, on every instance you can enable this parameter executing the following T-SQL:
DBCC TRACEON (3226, -1);
This will immediately start suppressing your backup messages for all databases. Huzzah.
However, as soon as your SQL Server service restarts, this trace flag will no longer be enabled, and your log will start filling up with successful error messages again. Sad trombone.
To resolve this, you need to enable the trace flag as a startup parameter. Get out your trusty SQL Server Configuration Manager and open it up. Click on SQL Server Services and select a SQL Server instance. Right-click on it and select Properties, then go to the “Startup Parameters” tab.
In the box under “Specify a startup parameter:” enter the following: -T3226
Just like this:
Now click “Add” and then “OK” and you’re get a nice message saying any changes won’t take effect until a restart. That’s OK (so you can click “OK”) because we already enabled the trace flag earlier.
There you go. Abracadabra, and your future self will be very thankful you did this!