The merge process could not update the list of subscriptions

Question

I have replication set up between a sql-server 2005 instance and multiple sql-server 2000 instances. The replication will successfully for a while before I get the following error message:

Violation of UNIQUE KEY constraint 'unique_pubsrvdb'. Cannot insert duplicate key in object 'dbo.sysmergesubscriptions'. (Source: MSSQLSERVER, Error number: 2627)

When I checked sysmergesubscriptions there were extra entries that appear to be coming from the 2000 instances.

My question is has anyone encountered this issue and how did you deal with it (without rebuilding the entire thing)

Answer 1

The problem was that one of the subscribers had old publications and subscriptions in the system tables that were replicated through out the entire system. Which caused the violation of UNIQUE KEY constraint.

Once we removed these old entires we were able to restart replication.

We were able to identify the valid records in sysmergepublication because we knew the state of this table before the invalid entries were replicated. This forum post shows you how to location invalid publications if you need to.

We used the follow sql to check for additional subscription entries:

select * 
  from sysmergepublications

select * 
  from sysmergesubscriptions 
 where pubid in ( select pubid from sysmergepublications) 

select * 
  from sysmergesubscriptions 
 where pubid not in ( select pubid from sysmergepublications)

Here is the sql that we used to delete the invalid subscriptions:

delete from sysmergesubscriptions 
      where pubid not in ( select pubid from sysmergepublications)

Note: the code sample above assumes that the sysmergepublication contains only valid publications