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_genhistorytable withgeneration=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.
Best Answer
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.