Hi all:
I'm attempting to set up replication from a SQL 2017 Instance to multiple subscribers. One is another SQL 2017 instance and the other is a SQL 2014 instance. The Distributor and Publisher are on the same instance and I'm setting up a push subscription to the two instances. After I set up the subscription, when I look at the Replication Monitor I see two sessions on the "Distributor to Subscriber History." One of them shows numerous actions where the records are bulk copied, etc. as the snapshot is propagated. The other, however, shows a message saying "The concurrent snapshot for publication "My_Publication" is not available because it has not been fully generated or the Log Reader Agent is not running to Activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for " (the message abruptly ends).
The log reader is definitely running, there are no errors in the errorlog, and test transactions are replicating immediately to both subscribers (so it appears to be working). So why the message?
This is a test run and I've got more adjustments to do before going live with this. I'm just trying to figure out what I did wrong when setting this up to see this kind of message. Should I not set up the two targets at the same time when I go through the wizard or what? I feel like if this was the problem, then I wouldn't have the option to set up multiple subscribers. SQL 2014 is within 2 versions of SQL 2017, so I know replication should be supported in this configuration.
I would appreciate any thoughts on what may have happened or what I should do differently on my next test run.
Check the log reader history in the distribution database and also check the distributor - the tables are MSlogreader_history and MSdistribution_history
Those can give you an idea if the snapshot was delivered and if there are transactions still to be delivered. The distribution agent would be applying snapshots and replicated records.
If there is a significant number of undelivered transactions that would be on the undistributed commands in replication monitor. You may need the distributor to run and may want to check the schedule. If the log reader history has a lot of messages like log records have been scanned in pass nn, n of which were marked for replication", you'd want to check the number of VLFs for the published database using dbcc loginfo.
Sue
Thanks, Sue. It's very informative. Did you mean, most of log-reader performance issues were caused by bad log settings(such as size, auto-growth-size)? If you run into this issue (log-reader performance issue which is caused by too many small VLFs), what will you do for a fast fix? You know, changing log setting doesn't 'merge' any existing VLFs.
GASQL.com - Focus on Database and Cloud