'Backup Completed' is NOT an Error Message


If you're like me, you like for things to be semantically reliable.  Huh?  

Said another way, I like for things to mean what they say and say what they mean. Here are a few examples of I get annoyed by failed sematics - when a footpath is used almost entirely by cyclists, when an escalator is merely stairs, or when a restroom has a huge crowd and long lines. (No rest to be had in that room).

So you can bet that I get a little prickly when the "Error Log" is used to post messages that something completed successfully. Really? I came here to this error log looking for, wait for it, ... errors! My biggest annoyance here in the SQL Server sphere is that SQL Server has been posting messages in the error log every time a backup completes successfully for, like, 300 years or something. I can remember that at least five years ago Microsoft storage PM Kevin Farlee blogged about it. And Kevin has been working on SQL Server for, like, 300 years. too.

I recommend that you set your SQL Server to use the startup traceflag DBCC TRACEON (3226) to disable backup success message. Read all about it on Microsoft's webpage describing this and many other trace flags for SQL Server.

In addition, be sure to look at Benjamin Nevarez's post on cool, undocumented trace flags. He still hasn't told me the secret trace flag that sends a sock to the Microsoft developer whenever the feature s/he developed causes an end-user to scream in frustration.

And don't overlook golden-oldies posts, like Andrew Kelly's blog chalked full of example T-SQL programs to demonstrate this scenario. I think this blog post was also written, like, 300 years ago - way before computers were invented. Very prescient of Andy!



-Follow me on Twitter

Discuss this Blog Entry 2

on May 24, 2012
Kevin, May I please say AMEN! It may be useful to see those completion messages when you have a manageable number of databases but on systems with numberous db's, that trace flag is invaluable. Three of the servers I manage have 150+ databases each and it is impossible to zero in on a real issue in the error log with thousands (I mean thousands- daily backups plus hourly tran log backups = thousands of entries!) of these benign messages filling up the error log! Thank goodness for the 3226 trace flag! Stefanie Higgins
on May 24, 2012
Kevin, It is annoying, but it can also be helpful. Knowing errors happened close to the backup times might point you in the direction that the backup caused an issue. Michael Dean

Please or Register to post comments.

What's Tool Time Blog?

SQL Server tools and tips from Kevin Kline.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×