Getting rid of those annoying backup successful messages

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

Share

Share on facebook
Share on twitter
Share on linkedin

One Response

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?