Introduction
One small issue you may not be aware of in SQL Server is it’s default behavior for logging successful database backup messages. By default, every successful database backup logs a message in the SQL Server error log. This happens for all backup types, for all of the databases on the instance. If you have a large number of databases that are doing frequent transaction log backups this can be a problem. This can cause your SQL Server error log to have a lot of information that is not normally very useful. You can alter this behavior with SQL Server Trace Flag 3226.
SQL Server Trace Flag 3226
Back in 2007, Microsoft’s Kevin Farlee wrote a blog post about Trace Flag 3226. This trace flag has been in the product since SQL Server 2000, so it has a long history. Despite this, when I ask the audience during many of my presentations, many people have not heard of TF 3226.
This is how Microsoft describes this trace flag:
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.
This trace flag stops successful database backups from logging a message in the SQL Server error log and the System Event log. Failed database backups will still log a message in the SQL Server error log, which is more important to know about. You can also still query the msdb database to see your database backup history.
Enabling SQL Server Trace Flag 3226
You can enable TF 3226 by running DBCC TRACEON (3226, -1). The -1 means that it is a global trace flag for the SQL Server instance. This trace flag goes into effect immediately, with no restart of the SQL Server Service required. The DBCC TRACEON command is documented here.
You also should add TF 3226 as a startup trace flag, so that it goes into effect every time SQL Server restarts. Figure 2 shows that this has been done in SQL Server Configuration Manager.
Final Words
I strongly recommend that everyone enables SQL Server Trace Flag 3226 and also sets it as a startup trace flag. Doing this should be part of your standard build and configuration procedure for SQL Server instances.
Another benefit from enabling TF 3226 is that it will eliminate one easy “finding” from most SQL Server Health Checks. Fixing easy configuration settings like this will force most consultants to look more deeply for configuration issues.
Please let me know what you think about this in the comments. Thanks for reading!
P.S. I have some additional scripts on my Resources page that are very useful for configuring and monitoring SQL Server instances.