Stop logging all successful backups in your SQL Server error logs

By:   |   Comments (6)   |   Related: > Backup


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.


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:


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


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)

TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test.bak' 

DBCC TRACEON (3226,-1)

TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test.bak' 


TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test.bak' 

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux 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 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

Comments For This Article

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.

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

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.


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

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 (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.

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


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 3, 2013 - 10:39:47 AM - Pavel Back To Top (25255)

Nice topic

get free sql tips
agree to terms