Getting rid of those annoying backup successful messages

Published On: 2011-05-16By:

If you have a SQL Server, and you are doing transaction log backups on the database server you’ll notice that 99% of the messages in your ERRORLOG file are worthless.  All they say is that the log was backed up successfully.  Personally I don’t care that every 15 minutes all 30 databases had their logs backed up without error.  I only care about the errors or problems when doing the backups.  I can easily enough write a script to query the msdb database and figure out when the last good backup was.

Fortunately there is a trace flag which can fix this little problem.  This trace flag is 3226.  Simply add -T3226 as a startup parameter for the SQL Server instance, and restart the instance, and the SQL Server will suppress the backup succeeded message from being put into the log.  Any errors which are thrown by the backup processes will still be logged as expected.  This makes the logs not only much easier to read, but also much, much smaller.

As far as I can tell this trace flag works on all versions from 2000 to “Denali”.

Many thanks to who ever it was at the PASS summit that pointed this little bit of awesomeness out to me.

Denny


Contact the Author | Contact DCAC

One response to “Getting rid of those annoying backup successful messages”

  1. csql says:

    Hi Denny, 

    How would you suppress the in the secondary server? Trace Flag 3226 does not help. Please let me know if there is any way to do this…
    Here is the reference..
    No answer found for this issue and i am facing similar issue,. 
    Any help would be appreciated. 
    Thanks,

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via