There’s a well known problem that every time a backup operation succeeds a message is written into the error log and Windows event log. If you’re taking very frequent log backups (say every 5 minutes) of multiple databases, that’s a significant amount of clutter in the logs. Well – now there’s a fix!
Kevin Farlee, the Storage Engine PM responsible for (among many other things) BACKUP and RESTORE has just blogged about a trace flag – 3226 – that’s been in the product since SQL Server 2000 and he’s planning to document this (and other) trace flags in this area starting in SQL Server 2008.
The trace flag stops successful backups from printing messages, so you only see messages when a backup fails. Backups always print failure messages when they fail, so this trace flag is safe to enable.
You can enable it using DBCC TRACEON (3226, -1), where the -1 turns on the trace flag for all connections without having to restart SQL Server, and you can make it a startup trace flag too.
I strongly recommend that everyone enables this trace flag – there are no downsides.