How do successful backups not be saved in the Error Log

With a single command:

If you run this way, you will be opening this Trace Flag to Global, and even if your SQL Server Instance restarts, this Trace Flag will automatically open again and you will see a record in SQL Error Log as follows:

DBCC TRACEON 3226, server process ID (SPID) 107. This is an informational message only; no user action is required.

That was a little brief. Let me explain. I’ve installed Log Shipping hundreds of times, and we may need to take Transaction Log backups of critical databases every five minutes, even if it takes 1 minute.

Most of the records in SQL Error Log, Default Trace and Windows Application Event Logs will be as follows.

Log was backed up. Database: , creation date(time): 2014/02/05(10:46:27), first LSN: 122:16:1, last LSN: 122:56:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘E:\BACKUPTRN\\_20140309134500.trn’}). This is an informational message only. No user action is required.

When we have a problem, the first things to check are the SQL Error Log, the Windows Event Logs, and the Default Trace files. If most of the content of these environments is full of successful backup messages as above, then it is very difficult for us to do so. That’s why I use this Trace Flag in environments where I have installed Log Shipping. I haven’t seen a negative side until now.

I would also like to mention; If you need to review Successful Transaction Log Backups, you can see the successful backup history from Default Trace even if Trace Flag 3226 is open.

However, when you use the 3226 Trace Flag, if your backup fails, you will see the error messages in SQL Error Log as follows:

BACKUP failed to complete the command BACKUP LOG . Check the backup application log for detailed messages.

In addition, if you want to read a simpler error log by removing the records you do not want to see in SQL Error Log, you can use the article “How To Filter SQL Server Error Log(Like or Not Like)“.