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

Cyborg gravatar image

10.6k 36 40 45

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)][1]. The workaround is to schedule the merge agent to run every minute or so. I changed the run mode and its working fine now

[1]: http://support.microsoft.com/default.aspx?scid=kb;en-us;304706∏=sql2k
more ▼

answered Sep 27, 2011 at 02:12 AM

Cyborg gravatar image

10.6k 36 40 45

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

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

@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



Answers and Comments

SQL Server Central

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



asked: Mar 16, 2011 at 02:17 AM

Seen: 1647 times

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