Turn On Trace Flag 3226

A few days ago, we looked at how to check for enabled trace flags and how they are turned on/off. Today, let’s look at how trace flag 3226 alters the way SQL Server logs backup information.

So Many Backups

How many databases are you managing on an instance? Only a few? Maybe a few hundred? If you’re taking frequent transaction log backups then the following may look familiar.

Let’s setup an example. I want to fill the log with transaction log backup messages so we’ll run the following by utilizing Ola Hallengren backup scripts:

EXECUTE dbo.DatabaseBackup
@Databases = 'ExampleDB',
@Directory = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ExampleDB\LOG',
@BackupType = 'LOG',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

WAITFOR DELAY '00:00:01'

GO 20

Do you recognize the above screenshot showing line after line of log backups? You may wish to hide these entries so that you can have an easier time scanning through real errors that you need to address.

Trace flag 3226 is the answer.

Before and After

Let’s enable trace flag 3226 by running the following:

DBCC TRACEON(3226);

We’ll take a full backup as a reference point. If we run our backup script above to take 20 transaction log backups, we’ll no longer see each one being logged.

Our new backups in a folder:

Our logged messages in SQL Server:

Still See What You Need

While Ola Hallengren’s backup scripts will have some error handling built in, it’s worth noting that log backup failures can still show in the error log. If I try taking a transaction log backup but purposely include a typo in the path:

I’ll still see entries for the error:

Trace flag 3226 is worth turning on and leaving on by adding it as a startup parameter. Chances are we have all had to deal with messages being logged in an error log (SQL Server or elsewhere) that shouldn’t have been designated as error messages. There’s a difference between error messages and informational messages. Turning on trace flag 3226 will keep log backup messages that are more informational out of the way.

Thanks for reading!