How and why to instruct SQL Server not to add entry to error log and system event log after each successful backup completion?
By default, SQL Server adds an entry in the SQL Server error log and in the system event log after each successful backup completion. For an instance where many backups are executed through out the day (for example if you take transaction log backups of every databases in every 15 minutes in a instance which has many databases), this default property of SQL Server can quickly fill logs. So to avoid such situation, sometime it makes sense to instruct SQL Server not to write to log every time it completes a successful backup operation.
Please enable trace flag 3226 to instruct SQL Server not add entry to error log and system event log after each successful backup completion. This trace can be a global or session level trace.
You can enable this trace in a global level using below command:
Alternately you can configure this trace part as a SQL Server startup parameter to switch on this trace globally.
For switching this trace on for a specific session, use below command in the session:
Before you switch on this trace, make sure that no scripts depend on entry of “Successful backup completion” in SQL Server log or system event log. Also personally I do not see it as a good practice to switch on this trace because more information is always better than less information. Also if you experience issue with reading the SQL Server error log or system event log for multiple number of “successful backup” entries, you can possibly use filter to read the log more efficiently.
Comments