2010-01-01

Replication: spot-check existing subscriptions for duplicate articles

The environment: SQL Server 2008 Enterprise 64-bit - transactional replication using a dedicated distributor.


SQL Server distribution agent generates error code 2627 with the following message:
Violation of PRIMARY KEY constraint 'PK_TableName'. Cannot insert duplicate key in object 'dbo.TableName'.

A quick manual inspection of the SQL Server error logs on the distributor reveals that there are two distribution agents generating the same error. If your publication is set to drop/recreate tables at the subscriber, it is possible to have duplicate replication - two different publications publishing the same article to the same destination server, database and table.


Removing one or both articles should resolve the issue (you may need to drop/add the article and re-snapshot). To guard against this error in the future, run the following query at the publisher database to determine if there are any other existing duplications:

select a.[name]     as articlename,
     s.srvname      as destination_servername,
     s.dest_db      as destination_database,
     a.dest_table   as destination_table,    
     count(*)       as numduplications
from syspublications p
     inner join sysarticles a on p.pubid = a.pubid
     inner join syssubscriptions s on s.artid = a.artid
group by a.[name],
     s.srvname,
     s.dest_db,
     a.dest_table
having count(*) >= 1


Before creating any new publications, or adding any articles to existing publications, run the following check for any tables you're adding to replication:
select a.[name]     as articlename,
     s.srvname      as destination_servername,
     s.dest_db      as destination_database,
     a.dest_table   as destination_table
from syspublications p
     inner join sysarticles a on p.pubid = a.pubid
     inner join syssubscriptions s on s.artid = a.artid
where a.[name] = 'tablename'

No comments:

Post a Comment