SQL Server - Using the 3226 traceflag to prevent writing backup informational messages to the SQL Server log

Views: 269
Reading Time: 4 minutes

Hey guys!

In this post, I would like to share with you the solution to a question you sent me today on Telegram, where you asked how you could hide the informational messages that the BACKUP DATABASE command produces during its execution and also write them to the SQL Server log. .

Introduction

If you have never backed up to SQL Server (!!!), a very simple example of using the BACKUP DATABASE command is like this, where I use the STATS = 10 parameter so that every 10% in the evolution of backup processing is output to track execution:

Result:

And looking at our SQL Server log, we can see that 2 records were generated because of this backup:

If you have multiple databases in your instance, including log backups being generated at a high frequency, these informational messages can end up consuming a lot of space and making it difficult to search for information when a problem really occurs in your environment.

Using the 3226 traceflag

To work around this scenario, we can use the 3226 traceflag, which will suppress these informational messages generated by the BACKUP DATABASE command. If you don't know it, traceflags are parameters used to temporarily modify some default database behavior. For the full list of SQL Server traceflags, go to Official SQL Server Traceflags Documentation.

Its use is very simple:

Result:

After performing the backup, let's review the SQL Server log again:

And as shown, we performed the backup and no informational message was generated, not even the log message. But will this traceflag suppress error messages as well? Lets test!

I will execute the command below, trying to back up a database that does not exist, to force an error:

And now let's see if this error was logged in the SQL Server log:

This is our error message. That is, warning messages have been deleted from the log and error messages are still being written.

Turning traceflags on and off

As we saw above, we can enable traceflag 3226 using the command below:

Once you perform this traceflag, it is enabled until the SQL Server service is stopped, meaning it does not have to be performed each time a backup is performed. If the SQL service is restarted, traceflag will need to be activated again.

If you want to stop the 3226 traceflag and return to default behavior without having to restart the SQL Server service, simply run the following command:

What if you want this traceflag to be enabled automatically whenever the SQL Server service starts? In this case, just change the startup parameter of the SQL Server service by accessing SQL Server Configuration Manager:

And now we add the -T 3226 parameter to the “Startup parameters” tab:

If you want to add more than one traceflag at SQL Server startup, simply add them separately:

Well guys, I hope you enjoyed this article and that it ends up being useful for you.
A big hug and see you in the next post.