Fed up with BACKUP success messages bloating your error logs?

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.


Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.