Replication: Identity column seeded incorrectly if two or more merge agents try to adjust the publisher range in parallel

  • Article

Problem:

SQL Server 2008

Merge replication with automatic identity range handling.

Suddenly, every insert on a specific table (TABLE1) stopped working with message:

The insert failed. It conflicted with an identity range check constraint in database 'DBNAME', replicated table 'dbo.TABLE1', column 'TblId'.

If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

But sp_adjustpublisheridentityrange does not solve the issue. Every insert keep failing with the same message.

Details on the identity ranges:

Check constraint: ([TblID]>(2528210) AND [TblID]<=(2529210) OR [TblID]>(2529210) AND [TblID]<=(2530210))

select top 1 TblID from TABLE1 order by TblID desc: returns 2525210

select IDENT_CURRENT('TABLE1 '): returns 2527348

=> 2525210 is the last correctly inserted record

=> 2527348 is the last failed insert, keeps growing while insert fails

=> <2528210, 2530210> valid range defined at the constraint

It looks as if the check constraint has moved forward, but not the id seed

Can be resolved by: DBCC CHECKIDENT (TABLE1 , RESEED, 2528211) that is, moving forward the seed in order to fall inside the range

Solution:

This issue was fix with the following hotfix:

You can download the hotfix files through one of the following Knowledgebase articles - there is a link at the top-left corner of the article that leads you to the download page:

Hotfix to apply to SQL Server 2008 (without Service Pack 1):

< https://support.microsoft.com/kb/973601/en-us >

Hotfix to apply to SQL Server 2008 with Service Pack 1:

< https://support.microsoft.com/kb/973602/en-us >

(KB article not yet published, will follow very soon)

We recommend you install the hotfix on your test environment before applying it to your production environment.

If you run into severe difficulties with this build, you can uninstall it through the Windows Control Panel -> Add and Remove Programs.