SQL Server with Mr. Denny

May 16 2011   2:00PM GMT

Getting rid of those annoying backup successful messages

Denny Cherry Denny Cherry Profile: Denny Cherry

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

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • CSQL
    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..
    http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/99686208-65ae-47c5-b928-be74b0d21321/

    No answer found for this issue and i am facing similar issue,. 

    Any help would be appreciated. 

    Thanks,
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: