By: Greg Robidoux | 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.
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
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
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
