Using Trace Flag 3226 to Suppress Log Backup Logging

Total: 1 Average: 3

Introduction

Every backup operation in SQL Server is written to the SQL Server Error log. This includes Transaction Log Backups even when they occur as part of a Transaction Log Shipping Configuration. Sometimes logging the entire Log Backup can be a nuisance in the SQL Server Error Log and needs to be managed. Trace Flag 3226 is used to suppress such logging and we will demonstrate how this can be done in this article.

Configuring Transaction Log Shipping

In order to demonstrate the value of this trace flag, we will implement a small log shipping configuration using a SQL Server 2017 database called Practice2017. Our primary instance is EPG-KIGIRI\I2017 and we are replicating this database to a SQL Server 2019 instance EPG-KIGIRI\I2019 (See Fig. 2). The entire configuration script is shown in Listing 1.

Log shipping configuration on primary

Fig. 1 Log Shipping Configuration on Primary

Code

Backup, copy and restore jobs are scheduled to run every five minutes, and whenever this happens, the database engine also writes an entry in the error log. This may be considered superfluous, as we can easily track the log backups using the SQL Agent job history.

Log shipping backup entries in SQL Error Log

Fig. 2 Log Shipping Backup Entries in SQL Error Log

Enabling Trace Flag 3226

Typically, we can enable trace flags either for the current connection or globally. We can use T-SQL to enable trace flags or implement the trace flag in the SQL Server startup parameters. It is recommended that you use the startup parameters approach to enable trace flags you want to apply to the instance. To apply the trace flag 3226 in the SQL Server startup parameters, follow these steps:

  1. Run SQL Server Configuration Manager as Administrator

Run SQL Server Configuration manager as administrator

Fig. 3 Run SQL Server Configuration Manager as Administrator

  1.  Right-click the desired instance and click Properties.

open instance properties

Fig. 4 Open Instance Properties

  1. Select the Startup Parameters

startup parameters

Fig. 5 Startup Parameters

  1. In the text box labeled Specify a startup parameter, type –T3226 and click Add.

Adding trace flag 3226 as a startup parameter

Fig. 6 Adding Trace Flag 3226 as a Startup Parameter

  1. Once –T3226 has been added to the list of Existing Parameters, click OK.

Trace flag 3226 added

The SQL Server error log shows that the trace flag is enabled on startup. (See Fig. 8)

Startup parameters indicated in the sql server error log

Fig. 8 Startup Parameters Indicated in the SQL Server error log

Viewing the Results

Once it is confirmed that the trace flag is working, we discover that the SQL Server error log no longer writes log backups associated with log shipping (or any other log backup) to the error log.  Pay close attention to Fig. 9 showing that all log backups stored in the backup job history are not written to the error log. This aligns with the point at which we enabled trace flag 3226 (about 8:15 PM).

No log backups recorded in SQL Server error log

Fig. 9 No Log Backups Recorded in SQL Server Error Log

If we also enable trace flag 3226 on the secondary instance EPG-KIGIRI\I2019, we find that the log restore operations are also no longer written to the error log since we enabled trace flag 3226 on the secondary instance at about 8:30 PM. (See Fig. 10)

No log restores recorded in SQL Server error log

Conclusion

In this article, we have demonstrated how we can use trace flag 3226 to suppress logging of transaction log backups on the primary instance, and the transaction log restores the log shipping settings on the secondary instance. This will be very useful to avoid unnecessary logging that could “hide” real issues popping up in the error log.

References

DBCC TraceOn Trace Flags

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud.
Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.
Kenneth Igiri