Benjamin Nevarez Rotating Header Image

SQL Server Error Log

Avoiding Backup Messages on the Error Log

One of the most useful trace flags I use on my SQL Server instances is trace flag 3226, which prevents SQL Server from writing those successful backup messages to the error log. By default, every time a database backup of any type is completed successfully, a message similar to the following is written to the SQL Server error log.

Log was backed up. Database: Test, creation date(time): 2010/06/28(14:53:06), first LSN: 21:2235:1, last LSN: 21:2235:1, number of dump devices: 1, device information: (FILE=3, TYPE=DISK: …

So when you perform many backups, especially transaction log backups, and/or have many databases on the same instance, the SQL Server error log could contain hundreds or thousands of these messages in a way that it can become difficult to find any other useful information there. When this trace flag is used, backup messages are no longer written to the error log or the system event log.

Although this trace flag had been available since SQL Server 2000, most of us learned about it until 2007 when both Andy Kelly and Kevin Farlee blogged about it. At the time it was undocumented but it is now totally documented, appearing on the Trace Flags entry of Books Online.

Trace flags can be set on and off by using the DBCC TRACEON and DBCC TRACEOFF commands or by using the -T startup option, although the latest choice is more appropriate for this specific case. One way to use –T startup option is by right-clicking on the SQL Server service using Configuration Manager, selecting Properties and the Advanced tab, and adding ;-T3226 to the Startup Parameters entry as shown in the next figure. Finally, you will be required to restart your SQL Server service for this configuration change to take effect.

clip_image002