THE SQL Server Blog Spot on the Web

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

Andrew Kelly

Successful Backup Messages No More...

How many times have you asked for a way to turn off the behavior in SQL Server that logs successful backup messages to the SQL Server Logs and to the Windows Application Event Logs? If you are like most DBA’s probably a lot. I have been asking for this feature for ages. I have sent email to SQLWish, filed on LadyBug and most recently on Connect with never any positive feedback. Well the other day I happened to be on campus in Redmond and was talking to Kevin Farlee (Thanks Kevin) who is a PM on the Storage Engine team for SQL Server.  I asked again if we could have this feature and he said he would get back to me. Well he did and the answer is the ability was already there in the form of a trace flag.  I could not believe this functionality was there all along and no one knew about it. Well at least I didn’t and I am pretty sure most others as well. So how do you use this functionality?  Pretty easy actually and here is an example.

First we will backup the Northwind database (you can substitute your own) to disk. We will then use another feature of SQL Server that I think is also under utilized, sp_readerrorlog to place the contents into a temp table so we can query against it. We then can see the backup message that was logged. 

 BACKUP DATABASE [Northwind] TO DISK = N'C:\Northwind_BU.bak' WITH INIT,STATS = 10

GO

 

IF OBJECT_ID('[tempdb].[dbo].[#ErrorLogs]',N'U') IS NOT NULL

    DROP TABLE [dbo].[#ErrorLogs]

 

CREATE TABLE [dbo].[#ErrorLogs]

    ([LogDate] DATETIME NULL, [ProcessInfo] VARCHAR(20) NULL, [Text] VARCHAR(MAX) NULL ) ;

 

INSERT INTO #ErrorLogs ([LogDate], [ProcessInfo], [Text]) EXEC [master].[dbo].[sp_readerrorlog] 0 ;

 

SELECT * FROM [dbo].[#ErrorLogs]

    WHERE [Text] LIKE 'Database Back%Northwind%' ;

 

 

Next we will turn on Trace Flag # 3226 and try it again. This flag can be set via TSQL or via a startup parameter.

 

DBCC TRACEON (3226)

GO

BACKUP DATABASE [Northwind] TO DISK = N'C:\Northwind_BU.bak' WITH INIT,STATS = 10

GO

TRUNCATE TABLE [dbo].[#ErrorLogs]

 

INSERT INTO #ErrorLogs ([LogDate], [ProcessInfo], [Text]) EXEC [master].[dbo].[sp_readerrorlog] 0 ;

 

SELECT * FROM [dbo].[#ErrorLogs]

    WHERE [Text] LIKE 'Database Back%Northwind%' ;

 

 

Notice that now the most recent backup is not in the log nor will it be in the Event log. No longer will we have to weed thru all those successful messages just to see if there was a failure.  Yes all failures will still be reported as before.  And if you want to get back to the default behavior just turn the flag back off as such:

 

-- To turn the behavior off

DBCC TRACEOFF (3226)

 

Published Monday, October 29, 2007 2:09 PM by Andrew Kelly

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

 

Tom Moreau said:

Is this flag officially documented or is it an undocumented-use-at-your-own-risk type of thing?

October 29, 2007 1:30 PM
 

Andrew Kelly said:

I am told it will be documented in Katmai. That's a good thing ey:)

October 29, 2007 1:31 PM
 

Linchi Shea said:

It should have been the other way around in the first place. In other words, if someone wants to see that informative message, he should do somethig to turn it on. For me, I'm perfectly fine with the no-news-is-good-news approach as the default.

October 30, 2007 10:04 AM
 

AaronBertrand said:

Andrew, I have received positive feedback on Connect #266957 (actually just last week).  See:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=266957

October 30, 2007 10:32 AM
 

Kevin3NF said:

Thanks Andrew....we manage several hundred SQL instances, many of which have hundreds of databases.  You can imagine the size and uselessness of the associated errorlogs, eh?

Good option to present to our customer base.

Kevin3NF

October 30, 2007 12:45 PM
 

kfarlee said:

Linchi,

If I was designing it now, that's how I'd do it.  Unfortunately as far as we can determine, that message has been there since Sybase days.

By now, there are hundreds of scripts that are depending on that message for some obscure management function, and I'd hear the screams from here if I took it out.  So, this is the next best thing.

Expect this to make it into more regular syntax a'la Aaron's Connect item when I can get it in.  Likely Katmai +1.

October 30, 2007 4:15 PM
 

Mark Hedtke said:

Very enlightening post for those who barely have time to come up for air. There is usually alot we miss when software turnover prevents us from digging deeper to find tidbits like this.  One of the keys to making any code work is education.  At the public education institution level, a great deal of the vital information like this never sees the day of light.  In fact many institutions are still offering SQL Server 2000 and still testing for certification, although public support for it by Microsoft literally ends on Feb 2008.  Certainly is great to be certified on dated material just in case a few hundred thousand sites are still using it.

November 27, 2007 2:54 PM
 

Michael Abair said:

This has saved me big as I have thousands of databases (500 per instance) getting 23 log backups and 1 full backup per day!  What a headache.  One thing to note for others in my shoes if you use database maintenance plans to run your backups remember that those traces are connection specific.  You can get around that by adding the trace flag to the sql server services startup parameters (this makes the trace global).  Heres a link detailing how

http://www.sqlservercentral.com/Forums/Topic421160-357-1.aspx?Update=1

December 27, 2007 2:11 PM
 

Kevin Kline said:

I recommend that you set your SQL Server to use the startup traceflag DBCC TRACEON (3226) to disable backup success message...

May 17, 2012 10:48 AM
 

SCP said:

Hi Andrew,

How would you get rid of messages from the secondary server? trace 3226 does not help:

Here is the reference question but no one has answered it yet.

http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/99686208-65ae-47c5-b928-be74b0d21321/

Please let me know if you happen to know any solution for this.

Thanks,

SCP

January 21, 2013 1:51 PM
 

Andrew Kelly said:

I don't know of any way to rid the log of the restore messages you are concerned with. I see why one would want to supress them just like the backups but so far I haven't found a trace flag for that. If I do find one I will post here.

January 22, 2013 4:43 PM
 

suzn said:

what is the difference between setting trace flag (3226) by T-SQL command and by adding it to start up parameters ??

DBCC Traveon(3226,-1) vs -T 3226

March 18, 2014 4:11 AM
 

Andrew Kelly said:

If you issue the DBCC command via TSQL it will only stay active until the next restart of SQL Server. By adding it to the start up parameters it will ensure it is always in effect.

Andy

March 18, 2014 8:42 AM

Leave a Comment

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