0

I have one Merge Replication setup with one Publisher and 5 to 6 Subscribers. We have added one additional subscriber to this replication and when we try to sync it's giving the following error.

Error Messages

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_genhistory_75711cc468114cd2ad98d5573a252083' and the index name 'c2MSmerge_genhistory_75711cc468114cd2ad98d5573a252083'. The duplicate key value is (1334936). (Source: MSSQLServer, Error number: 1505)

Actions Performed to Resolve Issue

I tried the following actions, but it did not help resolve the issue.

  • Created New Snapshot, but didn't work.
  • Changed agent profile with verbose history/big query timeout, but still didn't work.
  • I found one duplicate entry in the dbo.MSmerge_genhistory table with generation=1334936. I removed one entry and tried to sync, but it still didn't work. Then I removed both entries and kept them in a backup table and tried to sync, but again same error.

Kindly suggest what to do.

  • 1
    What version of SQL Server? Also, as per this link, when you removed the duplicate entry, did you re-generate the snapshot or just re-run the merge agents? – HandyD Apr 8 at 7:02
  • I have only re run the merge agent job.I have not generated the snapshot after removing the duplicate entry.Do I need to regenerate the snapshot...? – AnilNamdeo Apr 8 at 9:36
  • Yes, once the duplicate is removed, you need to regenerate the snapshot. This is because the snapshot is a series of files with the exported data. Removing the duplicate entry from the DB doesn't remove it from the snapshot files, only regenerating the snapshot will do this. – HandyD Apr 9 at 0:33
  • Thank you HandyD for your response......I have removed both the records from the MSmerge_genhistory table and kept it in a backup table.So do I need to readd 1 entry in MSmerge_genhistory table.And if this the case so which entry I have to add as timings for both the records are same. – AnilNamdeo Apr 9 at 6:29
  • I would suggest you look at cleaning up the merge metadata rather than manually adding/removing records from these system tables, that should help workaround this issue – HandyD Apr 9 at 6:37
0

Adding comment answer as answer

The solution was to remove one of the duplicate records from the MSmerge_genhistory table and then re-run the snapshot agent to generate a new snapshot.

This is because the snapshot is a series of flat files with the exported data. Removing the duplicate entry from the DB doesn't remove it from the snapshot files, only regenerating the snapshot will do this.

Another possible option is cleaning up the merge metadata first then generating the snapshot.

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.