TraceFlag 3226 – Successful log backup notification removed from error log

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

 

If you have an SQL instance with many databases and have tried to locate information in the SQL Error log or system event log it can be a little difficult to find. SQL Server writes by default in both SQL Error log and system event log when doing backups, both successful and failed. If you have 500 databases and running log backups every 15 minutes, it will be 2000 rows in SQL Errorlog and 2000 rows in system event log per hour with information of successful backups.

If you need to use those log files for troubleshooting, you have very large files to look in where most of the information is not about what possibly went wrong.

I therefore prefer to use Trace Flag 3226. With trace flag 3226 activated SQL do not write successful log-backups to SQL Error log or Windows Event log. Thus, you get less spam and more relevant information in the error log and event log.

This will not stop SQL Server writing in the log files if backups is failing.

You can activate trace flag 3226 as a start-up parameter for the SQL Server service. It requires a reboot so it is a good thing to do during installation of your SQL Server. If you are not able to restart, you can turn it on via DBCC TRACEON (3226, -1) but then it will just be running until the next restart of your SQL Service or if you deactivate it with DBCC TRACEOFF (3226,-1).
(The -1 is so that it runs global on all sessions and not just on the one you are executing the command in)

Check your Trace Flag status with DBCC TRACESTATUS.

NOTE – Do you have monitoring based on these messages in the logs so you should not activate this.

GUI:

T3226_1

CODE:

--Enable Trace Flag 3226
   DBCC TRACEON(3226,-1)
--Check Trace Flag Status
   DBCC TRACESTAUS
--Disable Trace Flag 3226
   DBCC TRACEOFF(3226,-1)

 

If you want to know more about traceflags I can recommend http://www.sqlservice.se/updated-microsoft-sql-server-trace-flag-list/

Good Luck!