x

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

more ▼

asked Mar 16, 2011 at 02:17 AM in Default

avatar image

Cyborg
10.8k 37 54 51

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, 2011 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, 2011 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, 2011 at 06:40 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Sep 27, 2011 at 02:12 AM

avatar image

Cyborg
10.8k 37 54 51

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, 2011 at 02:42 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 16, 2011 at 07:21 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

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

Mar 16, 2011 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, 2011 at 02:57 AM Cyborg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x26
x2

asked: Mar 16, 2011 at 02:17 AM

Seen: 2199 times

Last Updated: Mar 06, 2012 at 01:04 PM

Copyright 2016 Redgate Software. Privacy Policy