Merge Replication Identity Range not updating

I have a merge replication running between Seven SQL Server 2000, and the identity management is configured as Automatic. Now the problem is that, at one of our subscriber the identity range of a table get filled up its range and the publisher is not assigning or updating new values to that table. I tried sp_adjustpublisheridentityrange also but i was unlucky. Why does the publisher not assigning any new identity range for the subscriber?

Many thanks

asked Mar 16 '11 at 02:17 AM in Default

Cyborg
10.1k ● 29 ● 39 ● 44

I don't know why, could be a SQL 7 > 2000 issue.

You can re-assign the identities by hand by reseeding the identity column, that sould work. DBCC CHECKINDENT with the reseed option should do the trick.

Mar 16 '11 at 03:00 AM WilliamD

Sorry william, i doesn't mean 'SQL 7' it is 'between Seven SQL Server 2000'. I will change it on my post :)

Mar 16 '11 at 03:26 AM Cyborg

when you say you were unlucky, I guess there was an error message of some sort? If so can you add that to your question please?

Mar 16 '11 at 06:40 AM Fatherjack ♦♦

(comments are locked)

2 answers:

When merge agents runs in continues mode, Sometimes SQL server fails to generate new set of identity values for merge replication. This is confirmed as a bug in (SQL Server 2000 Only). The workaround is to schedule the merge agent to run every minute or so. I changed the run mode and its working fine now

answered Sep 27 '11 at 02:12 AM

Cyborg
10.1k ● 29 ● 39 ● 44

Nice work on finding the reason and applying the fix @Cyborg and many thanks for coming back and adding the solution to the forum. I am sure there will be a lot of people who will benefit from this information.

Sep 27 '11 at 02:42 AM Fatherjack ♦♦

(comments are locked)

Is the subscriber failing to complete its transaction? I have seen symptoms like this where a transaction at a subscriber fails because the rows inserted require more IDs that are available and the new range cannot be accessed with the transaction.

Subscriber1 has range 0-2000.
Subscriber uses 1500 IDs successfully
Subscriber starts transaction that attempts to insert 600 rows.
Transaction fails and rolls back.

answered Mar 16 '11 at 07:21 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

@Fatherjack, i didn't see any transaction failing at the subscriber.

Mar 16 '11 at 08:45 PM Cyborg

This was a major problem with my SQL server 2000 production servers running merge replication, users were unable to do any transaction when these identity range exhausted. Restarting merge agent is a solution (But i am not sure about the data loss) another option i did is manually updated the MSMerge_Identityrange system table and i assigned new set of ranges across the subscriber, i didn't got into any trouble by doing so :).

Sep 27 '11 at 02:57 AM Cyborg

(comments are locked)