3

Our SQL Server 2016 already has Merge replication running successfully for some tables in 2 databases. We then needed to restore Snapshot replication for another set of tables between same pair of databases. I have run a T-SQL script from old server to restore Snapshot publication/subscription. Everything went fine, publication and subscription were created. I have manually run the Snapshot Agent for the first time and then manually triggered synchronization via the View Synchronization Status dialog.

All articles in the publication had Drop and Re-Create setting for the destination tables. After initial synchronization finished I have compared source and destination tables and have confirmed number of rows to be equal. Just what we needed.

However, I am surprised that after delivering the initial snapshot the replication process didn't finish like it does for example for Merge replication. Instead, the View Synchronization Status dialog reports synchronization as running but with No replicated transactions are available message, see below:

View Synchronization Status dialog

I then went to check synchronization history for this subscription and found out that the actual synchronization has been running for already 20+ hours without actual rows/tables copied. Looks like it has been waiting for new changes/snapshots all this time, see below:

Snapshot Replication History

The above image correctly reports bulk copying of changes around 3 am because the REPL-Snapshot agent job has been put on schedule to run at 3 am each day. However, from the SQL Server Agent Job monitor I see that REPL-Distribution, REPL-LogReader and REPL-QueueReader jobs for my snapshot publication are constantly running.

The question is: is the above behavior a normal one? Shouldn't Snapshot replication job just process a new snapshot and then quit? Or are we missing schedules for Queue and Log reader jobs too? I worry for not being able to free up Transaction Log if replication never finishes.

In the T-SQL script for this publication/subscription we had these lines:

exec [DB1].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
exec [DB1].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
GO

Not sure if they cause the synchronization to be always running. Any insight on what is going on here will be very much appreciated.

1

This is correct state of Distributor Agent. After delivering from snapshot (which was created at scheduled time by Snapshot Agent) it triggered action that has finished as "Delivered Snapshot from the (...)". Delivery is performed by a Distributor, which is always on, and looking for new actions (Snapshots). So in reality there are two jobs for Snapshot replication: Snapshot Agent triggered by schedule and Distributor Agent waiting (active) for any new actions. "No replicated transactions are available" means here, that there are no new snapshots to distribute.

By design Distributor Agent needs to run constantly and listen for new snapshots which are scheduled in Snapshot Agent.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.