This article guides you to stop logging successful backup messages in SQL Server Error Logs.
The SQL Server error log is where all of your database server’s actions and errors are written down. These log entries show the date and time it occurred, the type of event, the event number, a description, and what action the Database Engine took.
Usually, you’ll find errors here and success messages as well- for example, backup success messages will appear in this log when taking a backup to disk. Although these entries can be helpful as reminders of actions you have performed or things you may have forgotten about (e.g., BACKUP DATABASE), they also add up fast over time and begin to clutter your logs, decreasing their value. So how do you disable getting those success messages without failing backups?
Sometimes, DBAs configure transaction log backups for production databases every 5-10 mins. If you have 50 databases and each has a log backup running every 5 minutes, you have 50 log backup entries every 5 minutes. We do not want to overfill the logs to miss the valuable messages.
To suppress these backup messages, we can use the trace flag 3226. trace flag 3226 as a startup trace flag suppresses successful backup messages in the error log.
Launch the SQL Server Configuration Manager and click on Startup parameters. Specify a startup parameter in the section and add the trace flag -T3226 as shown below.
Another option is to use DBCC TRACEON and enable the trace flag globally.
DBCC TRACEON (3226,-1)Let’s enable the trace flag at the session level and take a backup for the AdventureWorks2019 database for the demo.
DBCC TRACEON (3226)
GO
Backup Database [AdventureWorks2019] to disk='AdventureWorks2019.bak'
Go
As shown below, we do not get any messages for the database backup in the SQL Server error logs.
Kommentare