THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

'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, three hundred years or something.  I can remember at least five years ago that Microsoft storage PM Kevin Farlee blogging about it.  And Kevin has been working on SQL Server for, like, three hundred 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, three hundred years ago - way before computers were invented.  Very prescient of Andy!

Enjoy,

-Kev

-Follow me on Twitter



Published Thursday, May 17, 2012 9:40 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Glenn Berry said:

I recently fought a battle (and ended up losing) to use TF 3226. The SysAdmins on the system were using Splunk to query the SQL Server Error Log, and they got very upset when I turned on TF 3226.

Their argument was "How do we know that the backups are succeeding if we don't see that message in the log?". I tried to explain that we would get a error message if the backups ever failed, plus we would notice that the transaction log percentage usage would go up, but they were not convinced.

I decided that that I had better things to do than argue with them about it...

May 17, 2012 11:09 AM
 

Andrew J. Kelly said:

Thanks for the kind words Kevin :)

May 17, 2012 11:10 AM
 

Sankar Reddy said:

+1 on losing this battle because some people (in this case a principal Architect + very Sr. DBA with decades of experience) don't understand how SQL Server works or even try to understand when someone is explaining to them.

Same guys removed Ola's script (which I put in few years ago) from production boxes and put back off the shelf maintenance plans.

Same guys recommend hinting ROWLOCK on every DML and NOLOCK on every SELECT. And if you use BEGIN TRAN then there will be deadlocks everywhere.

Face palm! As Glenn said, there are better things to do in life than argue with few folks.

May 17, 2012 3:30 PM
 

Steve Dybing said:

I hate to post a contradictory opinion but I find those messages useful. When we have network backups fail (more frequent than I'd like...), it's easier for me search a bit further down in the errorlog for a successful message than it is to open some other tool to ensure a subsequent attempt succeeded.

May 17, 2012 5:03 PM
 

KKline said:

Glenn and Sankar, I'm always at a loss when organizations refuse to trust the judgement of their technical experts.  

Andy, I also said you were 300 years old.  ;^)

May 17, 2012 5:13 PM
 

Sankar Reddy said:

Steve,

>>than it is to open some other tool to ensure a subsequent attempt succeeded.<<

To look at the SQL Server error log, you are already using the SSMS. From the same tool, you can query the msdb.dbo.backupset table to look at that information.

And there is also one other place where this info is stored. i.e Default Trace.

DECLARE @filename VARCHAR(255)

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  

FROM sys.traces  

WHERE is_default = 1;  

--Check all the databases that are backed up and restored and their success/failure state.

SELECT

*

FROM [fn_trace_gettable](@filename, DEFAULT) gt

JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id

WHERE EventClass = 115 --'Audit Backup/Restore Event'

ORDER BY StartTime;

And if there are many databases on the server and you take frequent log backups then most of the error log will be filled these useless messages.

May 17, 2012 5:50 PM
 

JohnStaffordDBA said:

Excellent - always wanted to suppress those successful messages and now I know it can be done. Already set the trace flag in production and will ensure it gets added to the startup parameters too.

May 18, 2012 5:25 AM
 

RichB said:

Any trace flag to make backup TLOG only spew 1 error?

Not the combination of 1 error that tells you why it failed followed by the trappable error that just says it has...!

R

May 18, 2012 10:22 AM
 

Steve Dybing said:

Sankar, no, I am not already using SSMS, I am reading the errorlog. When a backup fails, I get email notifications from one or more monitoring tools. I then remote into the server and open up the errorlog. It may not be the quickest, it may not be the best, but it's what I'm used to and it works for me.

And yes, the errorlogs are filled with these "useless" error messages but even notepad has searching funtionality.

I was not trying to say that my way is better than anybody else's, I was just trying to say that at least one DBA finds them useful.

May 18, 2012 6:35 PM
 

Adam Machanic said:

Steve: Perhaps, in that case, you should have a TF to turn them on. The rest of us can, then, not be forced to add a TF to get rid of what we consider to be an annoyance. And all will be well in the world :-)

May 19, 2012 12:40 PM
 

Steve Dybing said:

Adam, I can probably live with that...

May 24, 2012 11:46 AM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement