Speak 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:
|
1 2 3 |
BACKUP DATABASE dirceuresende TO DISK = 'C:\Temporario\dirceuresende.bak' WITH STATS = 10 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Ativa a traceflag DBCC TRACEON (3226, -1) GO -- Executa o backup BACKUP DATABASE dirceuresende TO DISK = 'C:\Temporario\dirceuresende.bak' WITH STATS = 10 -- Se preferir, já pode rodar um backup de log também para testar BACKUP LOG dirceuresende TO DISK = 'C:\Temporario\dirceuresende_20180921_215600.ldf' |
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:
|
1 2 |
DBCC TRACEON (3226, -1) GO |
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:
|
1 2 |
DBCC TRACEOFF (3226, -1) GO |
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 parameter -T 3226 in 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.
