Stop database backup information from appearing in the Error Log

This post is in response to a question from one of my blog readers. Whenever a Database or Transaction Log is backed up, a corresponding entry is logged in the SQL Server Error Log regarding the backup. On an instance which has quite a lot of databases or if the transaction log backup is scheduled to taken very frequently, the entries regarding the backup operation will eventually bloat the Error Log like in the below screenshot.

Is there a way to stop these messages from appearing in the Error Log?” was the question. Yes, it is possible. The Trace Flag 3226 has to be enabled as a start-up parameter as seen in this screenshot.

Once the SQL Server Instance is restarted after enabling this trace flag, the information about the successful backup operations will not be logged in the Error Log. However, whenever any backup fails it will be logged in the Error Log even with T3226 on.

6 thoughts on “Stop database backup information from appearing in the Error Log

  1. Amit Banerjee

    One more not frequently known fact.

    However, my 2 cents here: I would rather have this information printed to my errorlog and archive/cleanup my MSDB backup history. This information is very useful in data recovery cases where we have to quickly identify the last successful backup time for an inconsistent database.

    Reply
    1. PradeepAdiga Post author

      Agree with you Amit. It depends on ones requirement. Let the error log or msdb grow 😉

      Reply
  2. BalaKrihsna.B

    Hi Pradeep,

    if any LSN mismatch is happen we can use “restore header ” to find the last log backup with corresponding one.we dint find here right pradeep.

    Thanks
    BalaKrishna.B

    Reply
  3. sudhir

    Thanks Pradeep. That was a nice tip. I prefer storing backup info in msdb database and not to log back up info on sql server log. Because I check the sql server log for any errors ( as part of my daily DB Server Health Check task) and all this backup info is bloating the error log.

    Reply
  4. Pingback: Something for the Weekend - SQL Server Links 08/04/11

Leave a Reply