Failed Rows on Synchronization


  • I have a subscriber that wasn't synchronizing for a few days due to a timeout (and the agent never seemed to retry again it just said that a progress message hadn't been logged...and was errored out).  So I restarted the agent and the sync seemed to work but now its continously saying that its retrying several rows where most if not all fail.  My subscription is set to continuous sync and it just seems to be going through a continuous loop where it's retrying rows and almost all rows fail.  Usually when all changed get synced up it will say waiting 60 seconds before polling for changes...but i cant ever seem to get to that state.  Of course the subscriber is making data changes on their end so i may not get to that point but i am worried that i keep seeing all these failed rows being retried and they all fail.  Any ideas whats going on?
    Thursday, April 25, 2013 4:09 PM


All replies

  • Can you check the conflicts to see if these are PK violations?

    You may need to delete them from the subscriber to prevent this from happening.

    looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?

    Thursday, April 25, 2013 7:00 PM
  • So yes there are a bunch of conflicts happening and i think i've figure out why.  We have TableA that has a foreign key referencing TableB on column B1.  On the subscriber they are creating a row in TableB and then a row in TableA in which a column in TableA, A1, references the value in TableB, B1.  So the subscriber looks like this:

    Table A: A1                      TableB: B1

                12345                            12345

    But during replication its trying to insert the row in TableA first, before the row in TableB thus causing an error because of the foreign key:

    Table A: A1                     TableB: B1

               12345                             null

    Since there's a foreign key on TableA referencing TableB, col B1, it can't insert the row on TableA causing a conflict.  If i query the publisher I don't see a row for TableB but if I wait awhile eventually it syncs down but then im left with a conflict and the row in TableA is missing at the publisher.  The replication processing order seems to be getting mixed up because in our app the row in TableB should've been created before creating the row in TableA.  Will modifying the downloadgenerationsperbatch and uploadgenerationsperbatch help fix this?

    Thursday, April 25, 2013 7:22 PM
  • Yes, you can increase -UploadGenerationsPerBatch and the -DownloadGenerationsPerBatch parameters to avoid splitting parent and child changes across generation batches.  This is covered in How to understand Merge Replication article processing order.

    Another option is to mark your foreign key constraints as NOT FOR REPLICATION which will instruct the foreign key constraint to not be enforced if the Merge Agent is performing the insert.

    Brandon Williams (blog | linkedin)

    Thursday, April 25, 2013 9:43 PM
  • So i tried the not for replication option and checked beforehand that it wouldn't require subscribers to be reinitialized.  It didn't so I pulled the trigger and now every subscriber(republishing server) is errored out complaining of the snapshot.  So i generated a new snapshot and a new partition snapshot for one of our subscribers and it appears that its initializing from the snapshot but eventually still errors out along the way.  This is not good because i don't want our unsynced data changes at the republisher to get overwritten by the snapshot.  Any ideas whats going on or how to fix this?

    Friday, April 26, 2013 4:28 PM
  • How did you mark the foreign key constraints NFR?

    Please post exact error messages.

    Brandon Williams (blog | linkedin)

    Friday, April 26, 2013 4:31 PM
  • i went into the publication properties in SSMS and clicked on the articles that were throwing the constraint violations and marked in there not to replication foreign keys.  After doing so invalidated the snapshot causing the subscribers to error saying the snapshot was invalid.  So i regenerated new snapshots and one of the republishers starts downloading for the snapshot, i see messages like applying schema script..., and eventually applying file xxx.bcp, but eventually still fails saying the merge process was unable to deliver the snapshot to the subscriber.  I'm hesitant at working to fix the subscribers cause if its trying to download from the snapshot will it overwrite my unsynced data changes? 
    Friday, April 26, 2013 5:00 PM
  • I'm sorry, I should have been more clear.  That is not how you mark foreign key constraints as NOT FOR REPLICATION.  Please see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION, ALTER TABLE, and Disable Foreign Key Constraints for Replication.

    To mark a foreign key constraint as NOT FOR REPLICATION using T-SQL:

    ALTER TABLE [dbo].[store] DROP CONSTRAINT [fk_tax_store_defaultcitytax];
    ALTER TABLE [dbo].[store] WITH CHECK ADD CONSTRAINT [fk_tax_store_defaultcitytax] FOREIGN KEY([defaultCityTax]) REFERENCES [dbo].[tax]([tax_id]) NOT FOR REPLICATION;

    Please enable agent logging with -OutputVerboseLevel 4 and post the error back here:

    Brandon Williams (blog | linkedin)

    Friday, April 26, 2013 6:52 PM