Stop logging all successful backups in your SQL Server error logs

By: Greg Robidoux | Updated: 2008-03-18 | Comments (6) | Related: > 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.

Backup1

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

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2008-03-18

Monday, May 7, 2018 - 4:52:12 PM - Matt	Back To Top (75886)
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.

Wednesday, October 23, 2013 - 11:51:03 AM - Greg Robidoux	Back To Top (27244)
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.

Monday, June 3, 2013 - 10:39:47 AM - Pavel	Back To Top (25255)
Nice topic