Share via

When is too much success a bad thing?

  • Article

I was talking to a customer the other day who had an interesting problem:  Successful backups.

Specifically, their problem had to do with the success messages that SQL backup puts in the SQL errorlog and the system event log.

Seems like a nice, friendly thing to do right?  Drop a note that your backup was successful, note the LSN, etc.

The problem comes when you do a LOT of backups.  In this case, the customer was doing 1 T-log backup per minute on EACH of 5 production databases.  That adds up to an errorlog entry every 12 seconds.  As you can see, the errorlog very quickly gets bloated with success messages.  This in turn causes two issues:

  1. Managing the size of the log itself
  2.  Finding anything actionable in the flood of success messages.

In this customer's case they had an added complication:  They were using a monitoring tool which scanned the SQL errorlog for certain error codes.  As it so happens, every so often the LSN noted in a success message just happened to match with some error condition, and the DBA got woken up to deal with his "corrupt database".

So, I went spelunking, and found a jewel which might save some of you some problems.

There is a traceflag, 3226, which suppresses these success messages from both the SQL errorlog and the system event log.  By enabling this traceflag you will no longer get the flood of success messages in your logs.  Of course, if you've implemented some sort of logic which depends on the content of these messages, that would be a bad thing, but for most people who do very frequent backups, this could be an asset.

This traceflag has been in the product since SQL 2000 so any existing instances can take advantage of it immediately.

 

Kevin Farlee

Storage Engine PM

Comments

  • Anonymous
    October 30, 2007
    This customer? :) http://sqlblog.com/blogs/andrew_kelly/archive/2007/10/29/successful-backup-messages-no-more.aspx

  • Anonymous
    November 07, 2007
    Adam, It seems like a undocumented trace flag. I could not find it in BOL. Are there any other usefull flags that can be used but are not available in BOL. Thanks. Najm

  • Anonymous
    November 07, 2007
    This is a trace flag which has been in the product, but undocumented since SQL 2000.  I'm currently in the process of getting it documented.  It's just too useful and little to no risk.

  • Anonymous
    November 23, 2007
    I SOOOO need this for one of my clients!!  They have 6500+ databases on one server and the error log is essentially useless due to the number of 'backup successful' messages.  I just tested this and fortunately it has no effect on the msdb tables, which I use for a home-grown log shipping system.

  • Anonymous
    December 12, 2010
    This is really interesting and is helpful, I'll start using in my Dev instances first before doing it in production