Posted by: Denny Cherry
SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2
If you have a SQL Server, and you are doing transaction log backups on the database server you’ll notice that 99% of the messages in your ERRORLOG file are worthless. All they say is that the log was backed up successfully. Personally I don’t care that every 15 minutes all 30 databases had their logs backed up without error. I only care about the errors or problems when doing the backups. I can easily enough write a script to query the msdb database and figure out when the last good backup was.
Fortunately there is a trace flag which can fix this little problem. This trace flag is 3226. Simply add -T3226 as a startup parameter for the SQL Server instance, and restart the instance, and the SQL Server will suppress the backup succeeded message from being put into the log. Any errors which are thrown by the backup processes will still be logged as expected. This makes the logs not only much easier to read, but also much, much smaller.
As far as I can tell this trace flag works on all versions from 2000 to “Denali”.
Many thanks to who ever it was at the PASS summit that pointed this little bit of awesomeness out to me.