August 20, 2018 at 9:16 am
Hi All
This seems like a strange one to me. We added a few new articles into a publication yesterday, and then reinitialised the subscriptions with a new snapshot. It's failed to complete the sync because of Primary Key errors on the subscriber. These aren't duplicates that exist on the publisher, so it's almost as if the records exist more than once in the snapshot.
I feel like I'm out of options other than to reinitialise from a new snapshot, unless anyone has seen this kind of behaviour before?
Thanks in advance
August 20, 2018 at 9:36 am
Hello,
Looks like it is appending data to your existing tables. Is it complaining on newly added articles or existing ones(I am guessing existing tables)? Just curious, Do you have @allow_anonymous and @immediate_sync set to true for your Pub? Also, when you say reinitialized with a new snapshot, is it set to truncate objects?
August 20, 2018 at 9:45 am
Hello
Both allow_anonymouse and immediate_sync are set to 0 on that publication. All published objects are recreated at the subscriber during a reinitialise, I believe.
Interestingly, the rows that it is complaining about being duplicated were created around the time the snapshot was taken.
August 20, 2018 at 10:00 am
Interesting...If allow_anonymous and immediate_sync is set to false, the newly created snapshot should include only newly added articles(in theory). So the rows which it's complaining about, are they from newly added article(s) or existing articles? Loong looong ago, I remember we had similar issue where there were few triggers enabled on the subscriber which were the culprits inserting data to tables causing duplicate value issues. Just curious, are there any Identity columns involved here?
August 20, 2018 at 10:07 am
The objects complaining about the duplication were pre existing tables, but at the subscriber they have creation dates on them of yesterday not long after the snapshot was taken.
There are identity columns, most of the tables are keyed off identity columns.
August 20, 2018 at 10:17 am
WoundedParrot - Monday, August 20, 2018 9:45 AMHelloBoth allow_anonymouse and immediate_sync are set to 0 on that publication. All published objects are recreated at the subscriber during a reinitialise, I believe.
Interestingly, the rows that it is complaining about being duplicated were created around the time the snapshot was taken.
It doesn't necessarily apply a snapshot of all objects - in some scenarios it will generate a snapshot of the new articles only.
Check the snapshot agent history in the distribution database to see what it generated and check the distribution agent history to see what it delivered and applied.
Sue
August 20, 2018 at 10:25 am
Not sure how critical the subscriber is for your business to be available and how large the DB is, but I would recommend comparing data in pub and sub table(s) using Redgate SQL Datacompare or ApexSQL Data Diff and synchronize one time manually if that's feasible (Or possibly setup form scratch??). For GOTCHAs with ID columns in replication, please refer to this article. https://www.red-gate.com/simple-talk/sql/database-administration/the-identity-crisis-in-replication/
Also, there's an ugly way(Not recommended) to force replication to ignore these errors by modifying Dist Agent profile (By adding error numbers to -Skiperrors param). This will mess up the data consistency on the subscriber as the name suggests (Continue on Data Consistency errors).
August 20, 2018 at 10:28 am
Sue_H - Monday, August 20, 2018 10:17 AMWoundedParrot - Monday, August 20, 2018 9:45 AMHelloBoth allow_anonymouse and immediate_sync are set to 0 on that publication. All published objects are recreated at the subscriber during a reinitialise, I believe.
Interestingly, the rows that it is complaining about being duplicated were created around the time the snapshot was taken.
It doesn't necessarily apply a snapshot of all objects - in some scenarios it will generate a snapshot of the new articles only.
Check the snapshot agent history in the distribution database to see what it generated and check the distribution agent history to see what it delivered and applied.Sue
The Snaphost history says it generated a snapshot of 215 articles, the distribution history however gives a lot of errors/warnings where it's skipping .bcp files because "they have already hbeen applied by a previously interrupred snapshot". Could this be an indicator of the problem if it hasn't skipped some files within the snapshot?
August 20, 2018 at 11:01 am
WoundedParrot - Monday, August 20, 2018 10:28 AMSue_H - Monday, August 20, 2018 10:17 AMWoundedParrot - Monday, August 20, 2018 9:45 AMHelloBoth allow_anonymouse and immediate_sync are set to 0 on that publication. All published objects are recreated at the subscriber during a reinitialise, I believe.
Interestingly, the rows that it is complaining about being duplicated were created around the time the snapshot was taken.
It doesn't necessarily apply a snapshot of all objects - in some scenarios it will generate a snapshot of the new articles only.
Check the snapshot agent history in the distribution database to see what it generated and check the distribution agent history to see what it delivered and applied.Sue
The Snaphost history says it generated a snapshot of 215 articles, the distribution history however gives a lot of errors/warnings where it's skipping .bcp files because "they have already hbeen applied by a previously interrupred snapshot". Could this be an indicator of the problem if it hasn't skipped some files within the snapshot?
It looks like it's attempting to resume a previous application of a snapshot since the process can be interrupted and resumed. Here is some information on how that works:
How SQL Server 2005/2008 Replication resumes interrupted snapshot delivery
You would want to generate a totally new snapshot. Check if you have anything in the MSsnapshotdeliveryprogress table on the subscriber - execute sp_resetsnapshotdeliveryprogress to clear the data. Sometimes I've deleted the older snapshot files from the repldata share for the publication.
Sue
August 21, 2018 at 2:37 am
Sue_H - Monday, August 20, 2018 11:01 AMWoundedParrot - Monday, August 20, 2018 10:28 AMSue_H - Monday, August 20, 2018 10:17 AMWoundedParrot - Monday, August 20, 2018 9:45 AMHelloBoth allow_anonymouse and immediate_sync are set to 0 on that publication. All published objects are recreated at the subscriber during a reinitialise, I believe.
Interestingly, the rows that it is complaining about being duplicated were created around the time the snapshot was taken.
It doesn't necessarily apply a snapshot of all objects - in some scenarios it will generate a snapshot of the new articles only.
Check the snapshot agent history in the distribution database to see what it generated and check the distribution agent history to see what it delivered and applied.Sue
The Snaphost history says it generated a snapshot of 215 articles, the distribution history however gives a lot of errors/warnings where it's skipping .bcp files because "they have already hbeen applied by a previously interrupred snapshot". Could this be an indicator of the problem if it hasn't skipped some files within the snapshot?
It looks like it's attempting to resume a previous application of a snapshot since the process can be interrupted and resumed. Here is some information on how that works:
How SQL Server 2005/2008 Replication resumes interrupted snapshot deliveryYou would want to generate a totally new snapshot. Check if you have anything in the MSsnapshotdeliveryprogress table on the subscriber - execute sp_resetsnapshotdeliveryprogress to clear the data. Sometimes I've deleted the older snapshot files from the repldata share for the publication.
Sue
This is exactly what it was, thank you.
August 21, 2018 at 12:56 pm
WoundedParrot - Tuesday, August 21, 2018 2:37 AMSue_H - Monday, August 20, 2018 11:01 AMWoundedParrot - Monday, August 20, 2018 10:28 AMThe Snaphost history says it generated a snapshot of 215 articles, the distribution history however gives a lot of errors/warnings where it's skipping .bcp files because "they have already hbeen applied by a previously interrupred snapshot". Could this be an indicator of the problem if it hasn't skipped some files within the snapshot?It looks like it's attempting to resume a previous application of a snapshot since the process can be interrupted and resumed. Here is some information on how that works:
How SQL Server 2005/2008 Replication resumes interrupted snapshot deliveryYou would want to generate a totally new snapshot. Check if you have anything in the MSsnapshotdeliveryprogress table on the subscriber - execute sp_resetsnapshotdeliveryprogress to clear the data. Sometimes I've deleted the older snapshot files from the repldata share for the publication.
Sue
This is exactly what it was, thank you.
You are very welcome - thanks for posting back!
Sue
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply