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:
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:
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-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.