Prevent logging all the Backup(Successful) Events in your Error Log

Have you ever been in a situation where you were trying to analyze a particular error details from your error log and you are annoyed by whole bunch of “Database Backed up/Log was Backed up bla bla…” messages?? Let’s say your Instance has 20+ databases and most of them are Log shipped(assume every 10 Mins log backups) to it’s DR Server (This is a very common Scenario). If you see anytime in your error log, you’ll end up with frustration digging into what exactly you are looking for. Because SQL Server has a Bad habit of logging in each and every detail(Success or Failure) of backup Operation(s) by Default. You’ve to define all kinds of crazy filters in your logs to exclude the junk and look what exactly you are looking for. Basically, You’ll be seeing Error logs something very similar to what you are seeing in the below Screenshot.

So…Is there anyway where you can get rid of all these Successful Backup events being logged from your SQL Server Error Log?? The Answer is YES 🙂
Microsoft has introduced a TraceFlag 3226 which you can enable globally, which eliminates all these Junk(In most of the Cases) getting logged into error log. Let’s do a Quick Demo.

Let’s recycle the Error Log, Just to make sure that we’ve a clean log – easy to Demo. It should come up as shown below.

Now, let’s start the actual testing.
I’m taking a Full Database Backup and an immediate Log Backup. Now let’s review our Error Log in the below Screenshot.

It logged Both the Full Backup and the Log Backup Operations.
Let’s turn ON TraceFlag 3226 now and let’s take couple of Backups and turn OFF Trace Flag and again take a backup and see what get’s logged in our Error Log for all the above operations. Just to convince you folks, I’m again Turning OFF Traceflag again and show you the Error Log all at one glance(See Below Screenshot)!!

If you carefully observe the Error Log at the left the tst5 and tst6 backup events are not logged and once we turned off the trace flag tst7 has been succesfully logged.

By this time you should be thinking what about the Failed Backup Events, We need them to be logged all the time in our Error Log. Absolutely “Yes“!!! All the Failed Backup Events are Successfully logged even with this Trace Flag Turned ON. You can see that in the below Screenshot.