Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Stop logging all successful backups in your SQL Server error logs


By:   |   Last Updated: 2008-03-18   |   Comments (6)   |   Related Tips: More > Backup

Problem

The SQL Server error log is a great place to find information about what is occurring on your database server.  One helpful thing that each log offers is that it shows you all of the failures and/or errors that have occurred since the last start of SQL Server or since the last time you cycled the error log.  On the flip side of that, it also shows you success messages such as backups, logins, DBCCs, etc...  Although this may be helpful to log all of this information it unfortunately clutters up your error logs pretty quickly especially if you are doing a lot of database backups every day.  So how can you turn off all of those successful backup messages.

Solution

A trace flag has been introduced that allows you to turn off logging of all successful backups in your SQL Server error logs.  Although this has existed for some time it has not been documented until now in the SQL Server 2008 documentation.

This new trace flag is 3226 and below is a description of the trace flag.

Trace Flag Description
3226 By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.

With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.

(Source SQL Server 2008 Books Online)

This trace flag works for SQL Server 2008, 2005 and 2000.  I have not tried this in SQL Server 2000, but I have seen forum posts where people have said that this does work with SQL Server 2000 SP4.  I am not exactly sure when this was introduced or what service pack release enabled this trace flag for SQL Server 2000.

Turn on manually

When this trace flag is turned on by using DBCC TRACEON the setting is not global and only affects the current session.  In order to make this a global change you would need to use the -1 such as the following.

DBCC TRACEON (3226,-1)

To then turn this off you would use the DBCC TRACEOFF (3226, -1) command to turn it off globally.

If you only want to affect your current session you could just use this option:

DBCC TRACEON (3226) and DBCC TRACEOFF(3226)

Turn on via startup parameters

Another option to make this a global change is to add this parameter to your startup options, such as the following.

-T 3226

For more information about how to set startup options refer to this tip: SQL Server 2000 vs SQL Server 2005 Startup Parameters

Example

In this example, we are doing the following:

  • cycling the error log so we have a clean start
  • doing a backup without the Trace Flag set (this should show up in the error log)
  • turning the Trace Flag on
  • doing another backup (this should not show up in the error log)
  • turning the Trace Flag off
  • doing a backup without the Trace Flag set (this should show up in the error log)
sp_cycle_errorlog

BACKUP DATABASE [Test] 
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test.bak' 
GO

DBCC TRACEON (3226,-1)

BACKUP DATABASE [Test] 
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test.bak' 
GO

DBCC TRACEOFF (3226,-1)

BACKUP DATABASE [Test] 
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test.bak' 
GO

Here is the output.  We can see that only the first and last backups are logged and the backup that was not done while the trace flag was on is not logged.

 

Next Steps
  • If you have a lot of backups that occurring this would be a good idea to use this trace flag
  • It would be nice if Microsoft changes this around, so by default these entries are not logged and if you want to log them then you could use the trace flag
  • Check out these new documented trace flags


Last Updated: 2008-03-18


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, May 07, 2018 - 4:52:12 PM - Matt Back To Top

I know this is a fairly old post but is there an option to disable logs like "Log was restored. Database(...)". Similar to Dave Gugg, my error log is swamped but in my case it's becuase i log-ship multiple databases, all to the same secondary sql server. Everytime T-Logs are applied to a secondary database it gets reported in my log which gets extremely cumbersome/noisey.


Thursday, October 15, 2015 - 4:19:24 PM - Greg Robidoux Back To Top

Hi Dave,

The backups are still logged in the backup system tables in the msdb database, so you still have the history.

So no, I can't think of any situation where this would be a problem.

-Greg


Thursday, October 15, 2015 - 3:47:04 PM - Dave Gugg Back To Top

Hi Greg

Thank you for sharing.  My logs are swamped with TLOG backups that run every minute.  I'm trying to think of a disadvantage to turning this trace flag on but I can't think of anything.  Can you think of any situations where changing this setting could come back to bite you?

Thank you


Wednesday, October 23, 2013 - 11:51:03 AM - Greg Robidoux Back To Top

Hi Donna, if you use the DBCC TRACEON this only works while the server is running.  If the server is restarted that trace flag will no longer be running, so you would have to change the startup parameters to make sure that this is always running by using the -T parameter.


Wednesday, October 23, 2013 - 11:12:59 AM - Donna Back To Top

Hello,

If you set the trace flag using "DBCC TRACEON (3226,-1)" and then bounce SQL Server (or restart the whole box), then will this trace setting be lost?  In other words, do we have to use the -T startup parameter to keep the trace flag around?   Thanks!


Monday, June 03, 2013 - 10:39:47 AM - Pavel Back To Top

Nice topic


Learn more about SQL Server tools