How does “sync with backup” work in transaction replication?

Transaction replication has an option called “sync with backup”. This option can be configured for both the publication database and the distribution database. But how does it actually work when enabled? Let’s explore its behavior in detail.

The fundamental operation of transactional replication is described below.

As explained, the Log Reader Agent reads log records from the publication database and applies them to the distribution database.

Because of this mechanism, if the Log Reader Agent is not actively reading the transaction log, log truncation will not occur in the publication database. When replication is the reason preventing log truncation, the database’s log_reuse_wait status will be displayed as REPLICATION.

The transaction log - SQL Server

Learn about the transaction log. Every SQL Server database records all transactions and database modifications that you ...

When the “sync with backup” option is enabled, this log truncation behavior changes. There are three possible configurations for the “sync with backup” setting:

Enable “sync with backup” in publicatin database
Enable “sync with backup” only in distribution database
Enable “sync with backup” in publication and distribution database

1. Enable “sync with backup” in publicatin database

When “sync with backup” is enabled on the publication database, the Log Reader Agent only reads log records that have been included in a transaction log backup. Note that a log backup is required; a full database backup is not enough.

Therefore, to use “sync with backup” while running replication, it is essential to take regular log backups of the publication database.

However, when setting up transactional replication, the recovery model of the database does not need to be full—it is possible to configure replication even with the simple recovery model. That said, since log backups cannot be taken in the simple recovery model, if you plan to enable “sync with backup” while using transactional replication, we must set the publication database to the full recovery model.

If “sync with backup” is enabled on the publication database and no log backup has been taken, the Log Reader Agent will record the following message:

// message in Log Reader Agent history
Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.

2. Enable “sync with backup” in distribution database

Even if “sync with backup” is enabled on the distribution database, replication will continue regardless of whether a backup has been taken.

However, log truncation in the publication database will not occur until a backup of the distribution database is taken. This backup can be either a full backup or a log backup.

In other words, when “sync with backup” is enabled on the distribution database, replication itself will continue without interruption. However, since log truncation in the publication database is blocked until a backup of the distribution database is taken, it is crucial to perform regular backups of the distribution database.

// log_reuse_wait_desc in Publication database before taking distribution database backup.

3. Enable “sync with backup” in publication and distribution database

It is also possible to enable “sync with backup” on both the publication database and the distribution database. When “sync with backup” is enabled on both databases:

Replication from the publication database to the distribution database will not proceed until a log backup of the publication database is taken.
Log truncation in the publication database will not occur until a full backup or log backup of the distribution database is taken.

This configuration ensures that transaction logs are retained until they are safely backed up in both databases, but it also means that both log backups must be taken regularly to prevent excessive log growth and proceed replication.

Verification Commands for “Sync with Backup” in Transactional Replication

Below are several useful commands to check and configure the “sync with backup” setting, monitor log usage, and troubleshoot log truncation issues.

1. Check the Current “Sync with Backup” Setting

SELECT DATABASEPROPERTYEX(DB_NAME(),'IsSyncWithBackup')

2. Enable “Sync with Backup”

EXEC sp_replicationdboption @dbname = N'databaseName', @optname = N'sync with backup', @value = N'true'

3. Perform a Full Backup to a NUL Device

BACKUP DATABASE [databaseName] TO DISK='NUL'

4. Perform a Log Backup to a NUL Device

BACKUP LOG [databaseName] TO DISK='NUL'

5. Check the Reason Why Log Truncation is Not Occurring

SELECT name,log_reuse_wait,log_reuse_wait_desc FROM sys.databases

6. Monitor Log Space Usage

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    total_log_size_in_bytes / 1024 / 1024 AS TotalLogSizeMB,
    used_log_space_in_bytes / 1024 / 1024 AS UsedLogSizeMB,
    (used_log_space_in_bytes * 100.0 / total_log_size_in_bytes) AS LogUsagePercentage
FROM sys.dm_db_log_space_usage;

7. Check the Status of the Log Reader Agent

USE [distribution]
SELECT * FROM MSlogreader_history ORDER BY time DESC;