Written by 01:21 Database administration, Transaction Log

Using Trace Flag 3226 to Suppress Log Backup Logging

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

[expand title =”Code“]

-- Listing 1: Transaction Log Shipping Configuration Script

-- Execute the following statements on the primary to configure log shipping 
-- for database [EPG-KIGIRI\I2017].[Practice2017],
-- The script is to be run on the primary in the context of the [msdb] database.  
------------------------------------------------------------------------------------- 
-- Adding the log shipping configuration 

-- ****** Begin: Script to be run on the primary: [EPG-KIGIRI\I2017] ******


DECLARE @LS_BackupJobId	AS uniqueidentifier 
DECLARE @LS_PrimaryId	AS uniqueidentifier 
DECLARE @SP_Add_RetCode	As int 


EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 
		@database = N'Practice2017' 
		,@backup_directory = N'G:\Backup\LogShip\' 
		,@backup_share = N'\\Epg-kigiri\g$\Backup\LogShip\' 
		,@backup_job_name = N'LSBackup_Practice2017' 
		,@backup_retention_period = 1440
		,@backup_compression = 2
		,@monitor_server = N'EPG-KIGIRI\I2017' 
		,@monitor_server_security_mode = 1 
		,@backup_threshold = 60 
		,@threshold_alert_enabled = 1
		,@history_retention_period = 2880 
		,@backup_job_id = @LS_BackupJobId OUTPUT 
		,@primary_id = @LS_PrimaryId OUTPUT 
		,@overwrite = 1 


IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) 
BEGIN 

DECLARE @LS_BackUpScheduleUID	As uniqueidentifier 
DECLARE @LS_BackUpScheduleID	AS int 


EXEC msdb.dbo.sp_add_schedule 
		@schedule_name =N'LSBackupSchedule_EPG-KIGIRI\I20171' 
		,@enabled = 1 
		,@freq_type = 4 
		,@freq_interval = 1 
		,@freq_subday_type = 4 
		,@freq_subday_interval = 5 
		,@freq_recurrence_factor = 0 
		,@active_start_date = 20190113 
		,@active_end_date = 99991231 
		,@active_start_time = 0 
		,@active_end_time = 235900 
		,@schedule_uid = @LS_BackUpScheduleUID OUTPUT 
		,@schedule_id = @LS_BackUpScheduleID OUTPUT 

EXEC msdb.dbo.sp_attach_schedule 
		@job_id = @LS_BackupJobId 
		,@schedule_id = @LS_BackUpScheduleID  

EXEC msdb.dbo.sp_update_job 
		@job_id = @LS_BackupJobId 
		,@enabled = 1 


END 


EXEC master.dbo.sp_add_log_shipping_primary_secondary 
		@primary_database = N'Practice2017' 
		,@secondary_server = N'EPG-KIGIRI\I2019' 
		,@secondary_database = N'Practice2017' 
		,@overwrite = 1 

-- ****** End: Script to be run on the primary: [EPG-KIGIRI\I2017] ******


-- Execute the following statements on the secondary to configure log shipping 
-- for database [EPG-KIGIRI\I2019].[Practice2017],
-- the script to be run on the secondary in the context of the [msdb] database. 
------------------------------------------------------------------------------------- 
-- Adding the log shipping configuration 

-- ****** Begin: Script to be run on the secondary: [EPG-KIGIRI\I2019] ******


DECLARE @LS_Secondary__CopyJobId	AS uniqueidentifier 
DECLARE @LS_Secondary__RestoreJobId	AS uniqueidentifier 
DECLARE @LS_Secondary__SecondaryId	AS uniqueidentifier 
DECLARE @LS_Add_RetCode	As int 


EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 
		@primary_server = N'EPG-KIGIRI\I2017' 
		,@primary_database = N'Practice2017' 
		,@backup_source_directory = N'\\Epg-kigiri\g$\Backup\LogShip\' 
		,@backup_destination_directory = N'G:\Backup\LogShipCopy\' 
		,@copy_job_name = N'LSCopy_EPG-KIGIRI\I2017_Practice2017' 
		,@restore_job_name = N'LSRestore_EPG-KIGIRI\I2017_Practice2017' 
		,@file_retention_period = 1440 
		,@monitor_server = N'EPG-KIGIRI\I2017' 
		,@monitor_server_security_mode = 1 
		,@overwrite = 1 
		,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 
		,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 
		,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

DECLARE @LS_SecondaryCopyJobScheduleUID	As uniqueidentifier 
DECLARE @LS_SecondaryCopyJobScheduleID	AS int 


EXEC msdb.dbo.sp_add_schedule 
		@schedule_name =N'DefaultCopyJobSchedule' 
		,@enabled = 1 
		,@freq_type = 4 
		,@freq_interval = 1 
		,@freq_subday_type = 4 
		,@freq_subday_interval = 15 
		,@freq_recurrence_factor = 0 
		,@active_start_date = 20190114 
		,@active_end_date = 99991231 
		,@active_start_time = 0 
		,@active_end_time = 235900 
		,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT 
		,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT 

EXEC msdb.dbo.sp_attach_schedule 
		@job_id = @LS_Secondary__CopyJobId 
		,@schedule_id = @LS_SecondaryCopyJobScheduleID  

DECLARE @LS_SecondaryRestoreJobScheduleUID	As uniqueidentifier 
DECLARE @LS_SecondaryRestoreJobScheduleID	AS int 


EXEC msdb.dbo.sp_add_schedule 
		@schedule_name =N'DefaultRestoreJobSchedule' 
		,@enabled = 1 
		,@freq_type = 4 
		,@freq_interval = 1 
		,@freq_subday_type = 4 
		,@freq_subday_interval = 15 
		,@freq_recurrence_factor = 0 
		,@active_start_date = 20190114 
		,@active_end_date = 99991231 
		,@active_start_time = 0 
		,@active_end_time = 235900 
		,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT 
		,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT 

EXEC msdb.dbo.sp_attach_schedule 
		@job_id = @LS_Secondary__RestoreJobId 
		,@schedule_id = @LS_SecondaryRestoreJobScheduleID  


END 


DECLARE @LS_Add_RetCode2	As int 


IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database 
		@secondary_database = N'Practice2017' 
		,@primary_server = N'EPG-KIGIRI\I2017' 
		,@primary_database = N'Practice2017' 
		,@restore_delay = 0 
		,@restore_mode = 0 
		,@disconnect_users	= 0 
		,@restore_threshold = 45   
		,@threshold_alert_enabled = 1 
		,@history_retention_period	= 2880 
		,@overwrite = 1 

END 


IF (@@error = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

EXEC msdb.dbo.sp_update_job 
		@job_id = @LS_Secondary__CopyJobId 
		,@enabled = 1 

EXEC msdb.dbo.sp_update_job 
		@job_id = @LS_Secondary__RestoreJobId 
		,@enabled = 1 

END 


-- ****** End: Script to be run on the secondary: [EPG-KIGIRI\I2019] ******

[/expand]

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

-- Listing 2: Enable a Trace Flag

-- Turn on a trace flag for the current connection
DBCC TRACEON (3205);  
GO 

-- Turn on a trace flag globally
DBCC TRACEON (3205, -1);  
GO

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

Tags: , , , Last modified: September 22, 2021
Close