Frequent log backups can bury real errors under pages of success notices. Suppressing backup success messages keeps the SQL Server error log useful without turning off the backups themselves.
Count the Backup Success Messages First
I inspect the current and archived SQL Server error logs before changing startup settings. xp_readerrorlog can search for the backup success text. Count the matching lines and compare them with true backup failures or other errors in the same period. The wording varies by operation and version, so review a sample rather than trusting one search phrase. In my test, one full backup wrote a "Database backed up" line and a "BACKUP DATABASE successfully processed" line. The first search below finds only the second line, so the script counts both. Keep the original log files available under your normal retention policy. A noisy log can still contain evidence you need for another incident.
EXEC master.dbo.xp_readerrorlog 0, 1, N'Backup', N'successfully';
EXEC master.dbo.xp_readerrorlog 0, 1, N'Database backed up';
EXEC master.dbo.xp_readerrorlog 0, 1, N'Error';Know Exactly What 3226 Changes
Trace flag 3226 suppresses successful backup messages in the SQL Server error log. It does not disable backup commands, error reporting, or normal msdb backup history. Set it as a startup trace flag when the policy is to keep the log quiet after every service restart. A session-only or temporary enablement is easier to forget and harder to audit. I confirm that the instance startup parameters contain -T3226 and that the trace flag is active after a controlled restart. Do not restart a production instance just to clean today's log.
Backup failures remain important. Keep Agent alerts, backup monitoring, and restore tests in place. The trace flag is a log hygiene setting, not a monitoring system.
Verify History Still Lands in msdb
After the next scheduled backup, query msdb.dbo.backupset for the database, finish time, type, and media information. Compare it with Agent job success and your backup monitoring record. A backup row proves a backup operation was recorded, but the recoverability test is a restore. I keep restore drills independent of log noise decisions. If your organization purges msdb history, ensure retention still covers the period needed for operational review.
SELECT TOP (20) database_name, type, backup_start_date,
backup_finish_date, backup_size
FROM msdb.dbo.backupset
ORDER BY backup_finish_date DESC;Cycle Logs on a Schedule
Cycling the error log closes the current file and starts a new one. It does not delete old files by itself. Schedule sp_cycle_errorlog at a sensible interval so one file is manageable to open and search. Set the number of retained error logs to match your investigation and compliance needs. I review disk use and actual incident lookback before reducing retention. A daily cycle with too few retained files can erase context quickly during a busy week.
EXEC master.dbo.sp_cycle_errorlog;
EXEC master.dbo.xp_readerrorlog 0, 1, N'Backup';
Test the Backup Success Messages After a Restart
When the next planned service restart occurs, confirm 3226 remains active and the successful backup lines stay out of the new error log. Use DBCC TRACESTATUS to check the flag. Verify that a failed backup still raises its normal error and job failure in a safe test environment; do not sabotage a production backup to prove a point. The important chain is backup operation, Agent outcome, monitoring alert, msdb history, and restorable media. I check all five because a quiet log alone is a weak success criterion.
What does your on-call person search first during a storage incident? If the answer is the error log, keeping it readable has real operational value. It should not become an excuse to stop looking at the backup dashboard.
Separate Backup Success Messages From Failure Alerts
Trace flag 3226 removes successful backup noise from the error log, but it does not guarantee an alert for a failed backup job. Check Agent job outcome, Database Mail or monitoring route, and the operational dashboard. I trigger a harmless failure in a dedicated test job to verify notification rather than breaking a real backup. Keep the error-log search for actual backup failures in the runbook. A quieter file is useful only if failures remain loud through at least one dependable channel.
I also inspect backup jobs that use third-party software. Their logging behavior can differ from native BACKUP commands. The trace flag addresses SQL Server's success messages, not every vendor log line or agent message. Count again after the change to see which source remains noisy before adding another filter. Do not suppress error text merely because it contains the word "backup."
Retain Enough Logs to Investigate
Cycling the error log creates manageable files, but retention controls how far back you can search. Compare the cycle interval with the longest time between incident discovery and investigation. A weekend incident found on Monday needs its older files. I check the configured number of error logs and available disk space together. If the files are still huge after suppressing backup success messages, investigate other repetitive messages instead of cycling hourly until the evidence disappears.
What would you need to reconstruct a failed restore or backup chain? Keep error logs, Agent history, msdb backup history, and backup media retention aligned with that answer. Each has a different purpose. I document the startup flag and log cycle job on every replica or replacement instance. A rebuilt server should inherit the monitoring and retention design, not just the quiet-log setting.
Review the startup parameter on every node that can become primary after failover. A failover changes the active instance and its local error log. I compare settings node by node, then verify backup history and alerting on the current primary. This avoids celebrating a quiet log on one instance while another still floods its files or misses an alert path.
Keep the Setting Documented
Record why the startup flag exists, how to check it, and who owns backup alerts. Review it during upgrades and instance rebuilds. A newly built replica can inherit the jobs but miss the startup parameter. Check each node separately when an availability design uses multiple SQL Server instances. I also keep an inventory of other startup flags so this one does not disappear inside a long command line.
The fix is deliberately small. Count the noise, enable the documented suppression, verify history and alerts, then check the result after restart. That sequence keeps a cosmetic log change from hiding a real backup problem.
Related reading on this blog: Reclaiming Space and Performance: Database Backup History and T-SQL Script: How to Search for Multiple Values in ERRORLOG?.
A quieter error log is not fewer backups, it is a clearer place to find failures.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
