Quick Scan Report – Backup Messages In Log

What this check looks for

The proportion of the error log made up of Database backed up and Log was backed up messages. The finding is raised when they dominate. It needs sysadmin to read the error log.

Why it matters

SQL Server writes an entry to the error log for every successful backup. With log backups every fifteen minutes on twenty databases, that is nearly two thousand entries a day, and it is almost all of the log.

Do the arithmetic for a typical instance:

That is several thousand lines a day of “everything worked”.

Why it matters is not disk space. The entries are small. It matters because the error log is the first place you look when something has gone wrong, and at that moment you are searching for one entry among tens of thousands of identical routine ones. The specific costs:

• Real entries become invisible. A corruption message, an I/O warning, a failed login or a stack dump sits in a sea of backup confirmations. Skimming the log, which is what people actually do, stops working.
• The log file grows large, which makes sp_readerrorlog and the log viewer slow, and takes a lock on the file while reading.
• History gets thrown away sooner. With a fixed number of retained log files, a log that fills quickly means the retained history covers days rather than months.
• Monitoring tools that parse the log spend their time on entries they will discard.

The fix is trace flag 3226, and it is unusually clean:

• It suppresses only successful backup entries. Backup failures are still logged, in full. You lose the confirmations and keep the problems.
• It does not change backup behavior in any way.
• The information is not lost. Every successful backup is already recorded permanently in msdb.dbo.backupset, with more detail than the error log entry contains: size, duration, device, LSNs, compression and checksum state. That table is the authoritative backup history, and it is queryable.
• It can be enabled without restarting SQL Server.

The only reason not to use it is if a monitoring or compliance process parses the error log for successful backup confirmations. That is worth checking before enabling, and the right response is to repoint that process at msdb.dbo.backupset, which is better data anyway.

It is widely regarded as one of the two or three trace flags worth enabling on almost every instance.

How to confirm it yourself

How much of the log is backup noise:

CREATE TABLE #errorLog (
    [LogDate]     DATETIME,
    [ProcessInfo] NVARCHAR(100),
    [Text]        NVARCHAR(MAX)
);

INSERT INTO #errorLog EXEC sp_readerrorlog 0;

SELECT COUNT(*) AS [total_entries],
       SUM(CASE WHEN [Text] LIKE 'Database backed up%'
                  OR [Text] LIKE 'Log was backed up%'
                  OR [Text] LIKE 'BACKUP DATABASE successfully%'
                  OR [Text] LIKE 'BACKUP LOG successfully%'
                THEN 1 ELSE 0 END) AS [backup_entries],
       CAST(SUM(CASE WHEN [Text] LIKE 'Database backed up%'
                       OR [Text] LIKE 'Log was backed up%'
                     THEN 1 ELSE 0 END) * 100.0
            / NULLIF(COUNT(*), 0) AS DECIMAL(5,1)) AS [backup_pct]
  FROM #errorLog;

DROP TABLE #errorLog;

Anything above about half is the finding, and instances at 90 percent are common.

Whether the flag is already on:

DBCC TRACESTATUS(3226, -1);

Or every flag currently set:

DBCC TRACESTATUS(-1);

Check the startup parameters as well, because a flag set with DBCC TRACEON does not survive a restart:

EXEC xp_readerrorlog 0, 1, N'Registry startup parameters';

That entry lists the startup parameters SQL Server was launched with, including any -T flags.

And confirm the real backup history is intact, which is what makes the suppression safe:

SELECT TOP (50)
       bs.[database_name],
       bs.[type],
       bs.[backup_start_date],
       bs.[backup_finish_date],
       CAST(bs.[compressed_backup_size] / 1048576.0 AS DECIMAL(12,1)) AS [size_mb],
       bmf.[physical_device_name]
  FROM msdb.dbo.backupset AS bs
 INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.[media_set_id] = bs.[media_set_id]
 ORDER BY bs.[backup_finish_date] DESC;

That is better information than the error log entry, and it is the query to use for backup monitoring from now on.

How to fix it

Enable trace flag 3226, both now and in the startup parameters so it survives a restart.

Immediately, with no restart:

DBCC TRACEON (3226, -1);

The -1 makes it global rather than session scoped.

Then add it to the startup parameters, which is the part that makes it permanent. Use SQL Server Configuration Manager: instance properties, Startup Parameters, add -T3226. Do not edit the registry directly.

Confirm both:

DBCC TRACESTATUS(3226, -1);

Global of 1 means it is on now. The startup parameter means it will still be on after a restart.

Cycle the log so the effect is visible:

EXEC sp_cycle_errorlog;

Then look again in a day. The log should now contain entries worth reading.

Do the other half of the job at the same time, since this finding and the huge error log finding are the same problem:

-- retain more log files
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'NumErrorLogs', REG_DWORD, 30;

Then repoint anything that was watching the log for backup confirmations at the backup history instead. A query like this is a better check than log parsing ever was:

-- databases whose last full backup is older than a day
SELECT d.[name] AS [database_name],
       MAX(bs.[backup_finish_date]) AS [last_full_backup]
  FROM sys.databases AS d WITH (NOLOCK)
  LEFT JOIN msdb.dbo.backupset AS bs
         ON bs.[database_name] = d.[name] AND bs.[type] = 'D'
 WHERE d.[database_id] > 4 AND d.[state_desc] = 'ONLINE'
 GROUP BY d.[name]
HAVING MAX(bs.[backup_finish_date]) < DATEADD(DAY, -1, GETDATE())
    OR MAX(bs.[backup_finish_date]) IS NULL;

Keep msdb history tidy, since it now carries the whole record:

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '2025-09-15';

Run that on a schedule with a rolling date. Backup history that is never purged makes msdb large and the restore dialogs slow, and the tables involved have their own indexing problems at scale.

Set the flag on every instance, ideally as part of your standard build. It is one of the few trace flags that is close to universally appropriate.

How long it takes

About an hour, most of it setting the startup parameter and repointing any monitoring. The runtime change takes seconds.

Related reports

Related checks

Frequently asked questions

Will I lose my backup history? No. Every backup is recorded in msdb.dbo.backupset with far more detail than the error log entry. The flag suppresses the log message only.

Are backup failures still logged? Yes, in full. Trace flag 3226 suppresses successful backup entries only, which is precisely what makes it safe.

Do I need to restart SQL Server? Not to enable it now. Add -T3226 to the startup parameters so it survives the next restart.

Is this trace flag supported? Yes. It is documented by Microsoft and widely used. It changes logging only, not backup behavior.