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:
- Managing the size of the log itself
- 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.
This piece was a direct lift from the work of Kevin Farlee. I just thought to put it on here for the purpose of knowledge sharing. Hope you learned something.