merge replication identity range troubles

I have the following problem with merge replication (hoping I can put it
down in a clear, probably not concise, way).
1) We have a merge replication setup between a publisher and a
subscriber.
2) I have set up the subscriber in such a way, that at initialisation,
it al ready had all schema information and data. Not all tables are
replicated.
3) Identity ranges have been put in place.
Over time the following changes came around
1) Changed columns in published articles
2) adjusted identity ranges
To make these changes happen the following steps had been taken:
1) Drop subscription
2) Remove article from publication
3) make changes to the table
4) made changes on subscriber database
5) re-added article to publication
6) adjusted identity ranges
7) ran snapshot agent to create new snapshot
8) ftp'd new snapshot to subscriber server
9) recreated the subscription
10) had the snapshot applied (but since I already had all the schema
changes on subscriber side aswell as all the data, I did not choose to
apply those options)
11) initialisation went ok, verification as well (notification through
net sends)
And everything seemed to be working smoothly after the updates.
But now: Word got in from the office using the subscriber database
that they couldn't create anymore documents. The cause was soon found: the
identity ranges for the tables containing the documents were full: no more
records could be added.
Simply giving a kick to the merge agent would have solved that, but
alas: it did not.
Further investigation showed a discrepancy between the set identity
ranges on publisher side, and the stored identity ranges in the
MSrepl_identity_range table on the subscriber side.
subscriber:
exec sp_MShelp_identity_property @.tablename = N'MSGattributes'
output:
replicated=1,
uto_identity_support=1
identity_incremental=1
current_identity=8200264
next_starting_seed=8200000
max_identity=8400000
publisher_range=40000
subscriber_range=20000
threshold=80
in_repulishing=1
First note: there's a typo in the stored procedure.. the
threshold is incorrect and the publisher/subscriber range is incorrect. After
checking the code it became clear why the publisher_range and subscriber range
are these low values: they're simply divided by 10.
Further more: info for this particular table from the
MSrepl_identity_range table:
objid=2086298492
next_seed=8200000
pubrange=null
range=200000
max_identity=8400000
threshold=99
current_max=8399999
Publisher side:
exec sp_MShelp_identity_property @.tablename = N'MSGattributes'
replicated=1
auto_id_incr=1
id_incr=1
cur_id=7693746
next_seed=8400000
max_id=2147483647
publisher_range=200000
subscriber_range=200000
threshold=99
in_repulishing=0
After some serious digging through google, newsgroups and
various messageboards, I could not quite find a similar case and thus
solution. I did come across some interesting tips and hints, tried a couple of
those (using sp_MScheckidentityrange, sp_MSadjustmergeidentity etc.. it
proved rather difficult to find some form of documentation about these
undocumented sp's..), but to no avail.
Finally I decided to upload the latest snapshot and re-initialize the
subscription (pull subscription btw, forgot to mention this).
The merge agent gave me a nice error message: could notinsert double record
due to unique constraint on the MSrepl_identity_range table. Kinda made
sense, so I emptied the table (whether this is a foolish thing or not,
I don't quite know, documentation is hard to find on this subject), which
seemed to do the trick. The snapshot would be applied.
State before snapshot:
Identity range information for the tables in question were incorrect (range
of 20k, should have been 200k).
State after snapshot:
MSrepl_identity_range table showed correct ranges.
But the problem persists: no new records can be added to the tables in
question.
So, most likely I've been overlooking some crucial steps,
did some stupid things or am just unlucky somewhere.
One more thing: I checked the constraints on the tables on both sides:
There was some double identity range constraint set on one of the tables, I
double checked again on both sides and removed the one with the deviating
constraint name.
I am in need of a solution to this problem: how to fix the identity ranges
so that record insertion is possible again, since the merge agent does not
seem to be re-adjusting the identity ranges, even though they're all full.
Not really looking forward to re-building the subscriber database from
scratch, transfer all the data, snapshot, recreate the subscription etc..
which would be my ultimate last resort (but perhaps a bit foolish as well?).
***
While reformatting this post taken from mcse.ms to a pseudo readable
usenet mail, the thought occured to me that I could just synchronize,
verify all, disable updates (somehow...), remove the subscription, run a
snapshot, try to clear up as much as possible on the subscriber side,
re-create the subscription and hope for the best?
Gerald Raucamp - http://www.cavey.nl
> After some serious digging through google, newsgroups and
> various messageboards, I could not quite find a similar case and thus
> solution. I did come across some interesting tips and hints, tried a couple of
> those (using sp_MScheckidentityrange, sp_MSadjustmergeidentity etc.. it
> proved rather difficult to find some form of documentation about these
> undocumented sp's..), but to no avail.
>
Having written this, and just checking out the new
groups-beta.google.com, I've come across some similar problems. Still
investigating the problem, but any input/help/directions/pointers are
welcome!
Gerald Raucamp - http://www.cavey.nl
|||have you reviewed these articles?
http://support.microsoft.com/default...b;en-us;304706
http://support.microsoft.com/default...b;en-us;324361
I also make the second step of my merge agent
sp_MScheckidentityrange @.pubid=pubid, @.checkonly=0
"Gerald Raucamp" <caveyREMOVE@.THISvengeance.et.tudelft.nl> wrote in message
news:slrncsgete.o86.cavey@.vengeance.et.tudelft.nl. ..[vbcol=seagreen]
couple of
> Having written this, and just checking out the new
> groups-beta.google.com, I've come across some similar problems. Still
> investigating the problem, but any input/help/directions/pointers are
> welcome!
> --
> Gerald Raucamp - http://www.cavey.nl
|||You should let replication handle all your identity ranges automatically.
You should never have to manually adjust identity ranges.
Jim.
"Gerald Raucamp" wrote:

> I have the following problem with merge replication (hoping I can put it
> down in a clear, probably not concise, way).
> 1) We have a merge replication setup between a publisher and a
> subscriber.
> 2) I have set up the subscriber in such a way, that at initialisation,
> it al ready had all schema information and data. Not all tables are
> replicated.
> 3) Identity ranges have been put in place.
> Over time the following changes came around
> 1) Changed columns in published articles
> 2) adjusted identity ranges
> To make these changes happen the following steps had been taken:
> 1) Drop subscription
> 2) Remove article from publication
> 3) make changes to the table
> 4) made changes on subscriber database
> 5) re-added article to publication
> 6) adjusted identity ranges
> 7) ran snapshot agent to create new snapshot
> 8) ftp'd new snapshot to subscriber server
> 9) recreated the subscription
> 10) had the snapshot applied (but since I already had all the schema
> changes on subscriber side aswell as all the data, I did not choose to
> apply those options)
> 11) initialisation went ok, verification as well (notification through
> net sends)
> And everything seemed to be working smoothly after the updates.
> But now: Word got in from the office using the subscriber database
> that they couldn't create anymore documents. The cause was soon found: the
> identity ranges for the tables containing the documents were full: no more
> records could be added.
> Simply giving a kick to the merge agent would have solved that, but
> alas: it did not.
> Further investigation showed a discrepancy between the set identity
> ranges on publisher side, and the stored identity ranges in the
> MSrepl_identity_range table on the subscriber side.
> subscriber:
> exec sp_MShelp_identity_property @.tablename = N'MSGattributes'
> output:
> replicated=1,
> uto_identity_support=1
> identity_incremental=1
> current_identity=8200264
> next_starting_seed=8200000
> max_identity=8400000
> publisher_range=40000
> subscriber_range=20000
> threshold=80
> in_repulishing=1
> First note: there's a typo in the stored procedure.. the
> threshold is incorrect and the publisher/subscriber range is incorrect. After
> checking the code it became clear why the publisher_range and subscriber range
> are these low values: they're simply divided by 10.
> Further more: info for this particular table from the
> MSrepl_identity_range table:
> objid=2086298492
> next_seed=8200000
> pubrange=null
> range=200000
> max_identity=8400000
> threshold=99
> current_max=8399999
> Publisher side:
> exec sp_MShelp_identity_property @.tablename = N'MSGattributes'
> replicated=1
> auto_id_incr=1
> id_incr=1
> cur_id=7693746
> next_seed=8400000
> max_id=2147483647
> publisher_range=200000
> subscriber_range=200000
> threshold=99
> in_repulishing=0
>
> After some serious digging through google, newsgroups and
> various messageboards, I could not quite find a similar case and thus
> solution. I did come across some interesting tips and hints, tried a couple of
> those (using sp_MScheckidentityrange, sp_MSadjustmergeidentity etc.. it
> proved rather difficult to find some form of documentation about these
> undocumented sp's..), but to no avail.
> Finally I decided to upload the latest snapshot and re-initialize the
> subscription (pull subscription btw, forgot to mention this).
> The merge agent gave me a nice error message: could notinsert double record
> due to unique constraint on the MSrepl_identity_range table. Kinda made
> sense, so I emptied the table (whether this is a foolish thing or not,
> I don't quite know, documentation is hard to find on this subject), which
> seemed to do the trick. The snapshot would be applied.
> State before snapshot:
> Identity range information for the tables in question were incorrect (range
> of 20k, should have been 200k).
> State after snapshot:
> MSrepl_identity_range table showed correct ranges.
> But the problem persists: no new records can be added to the tables in
> question.
> So, most likely I've been overlooking some crucial steps,
> did some stupid things or am just unlucky somewhere.
> One more thing: I checked the constraints on the tables on both sides:
> There was some double identity range constraint set on one of the tables, I
> double checked again on both sides and removed the one with the deviating
> constraint name.
>
> I am in need of a solution to this problem: how to fix the identity ranges
> so that record insertion is possible again, since the merge agent does not
> seem to be re-adjusting the identity ranges, even though they're all full.
> Not really looking forward to re-building the subscriber database from
> scratch, transfer all the data, snapshot, recreate the subscription etc..
> which would be my ultimate last resort (but perhaps a bit foolish as well?).
>
> ***
> While reformatting this post taken from mcse.ms to a pseudo readable
> usenet mail, the thought occured to me that I could just synchronize,
> verify all, disable updates (somehow...), remove the subscription, run a
> snapshot, try to clear up as much as possible on the subscriber side,
> re-create the subscription and hope for the best?
> --
> Gerald Raucamp - http://www.cavey.nl
>
|||On 2004-12-21, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> have you reviewed these articles?
> http://support.microsoft.com/default...b;en-us;304706
> http://support.microsoft.com/default...b;en-us;324361
> I also make the second step of my merge agent
> sp_MScheckidentityrange @.pubid=pubid, @.checkonly=0
>
Thanks for the links. I have checked out the articles but unfortunately
they don't seem to apply to my situation.
The identity ranges are adjusted on the publisher without much of a
hickup using the sp_adjustpublisheridentityrange call. The problem lies
at the subscriber side. Afaik. I'll give it a try though, I might be
overlooking something.
The second link concerning the insert trigger does not seem to be an
issue either. I will check any triggers on the offending tables tomorrow
for the "not for replication" directive. We had some troubles in the
past (oversight is the correct word) with insert/update triggers firing
when the Merge agent was updating info...
I was wondering though what the "in_republishing" column means in the
sp_MShelp_identityrange call. Does that mean the table/article is being
republished on the subscriber side? If so, that would mean something is
a miss, since currently there is only one publisher and one subscriber
(more might be added in the future, depends on available IT
infrastructures at the various branche offices).
I'll also add the second step to my merge agent and will try to get more
verbosity in the agent log file.
Gerald Raucamp - cavey@.vengeance.et.tudelft.nl
http://www.cavey.nl
|||Hi Jim,
On 2004-12-21, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> You should let replication handle all your identity ranges automatically.
> You should never have to manually adjust identity ranges.
>
I currently have, when it's working, merge replication set to handle the
ranges automagically for me. Somehow though, the magic isn't working.
A short recap of my long opening mail:
There is a publisher and a subscriber, range handling set in the hands
of the merge agent.
Pull subscription, merge agent running on hte subscriber.
Inserts work fine on publisher and get replicated to the subscriber.
Inserts on the subscriber fail with the nice message:
The identity range managed by replication is full and must be updated
by a replication agent. The INSERT conflict occurred in database
'ocs', table 'MSGattributes', column 'pkCode'.
Sp_adjustpublisheridentityrange can be called to get a new identity
range.
This kinda only works on the publisher side.
quote taken from this KB322910:
The only way to obtain a new range for a subscriber table is to run the
Merge Agent.
This clearly isn't working and I'm trying to find out why.
So far the only thing "different" that I can find between the subscriber
and the publisher is the "in_repulishing" column from the output of the
sp_MShelp_identity_property @.tablename='MSGattributes' call.

> Jim.
>
Hilary,
I have read through the sp_MScheckidentityrange stored procedure, I
manually went through some steps to figure out what the stored procedure
was doing. It still is a bit unclear, but it seems to be checking whether
the ranges need a bump or not. It makes a check whether the article is
being republished, it is not, so it skips some extra steps. Due to the
@.checkonly=1 option there is no reseeding done. The result was a nice 0,
no bump needed.
Juggling around with sp_help 'MSGattributes' to check the constraints and DBCC
CHECKIDENT('MSGattributes',noreseed) to check current id, it falls
within the constraint interval.
So, for some strange reason the database thinks the ranges are full, and
yet everything seems to be indicating that the ranges are quite ok and
not full at all yet.
Kind regards,
Gerald Raucamp - http://www.cavey.nl
|||Ok,
I feel like an idiot now. It seems to be working now, I have no idea
what I have done to make it work, but at least the subscriber is
accepting inserts again.
I was put off track by an error message generated by the application on
the subscriber side. Past experience told us it had to do with the
identity range being used up, after fixing the ranges the problem still
persisted so I kept on looking within the database, while in fact the
problem may now be at the application side (xsl/xml transformations).
I would like to thank everybody for their time and I do hope the
database issue has been resolved now. I'm just feeling a bit *hrmz hrmz*
right now about the situation.
Gerald Raucamp - http://www.cavey.nl
|||I've had identity range problems in the past. One thing I seen happening
once was more than one identity range constraint on the same column.
It's always worth checking that before doing anything else.
Jim.
"Gerald Raucamp" wrote:

> Ok,
> I feel like an idiot now. It seems to be working now, I have no idea
> what I have done to make it work, but at least the subscriber is
> accepting inserts again.
> I was put off track by an error message generated by the application on
> the subscriber side. Past experience told us it had to do with the
> identity range being used up, after fixing the ranges the problem still
> persisted so I kept on looking within the database, while in fact the
> problem may now be at the application side (xsl/xml transformations).
> I would like to thank everybody for their time and I do hope the
> database issue has been resolved now. I'm just feeling a bit *hrmz hrmz*
> right now about the situation.
>
> --
> Gerald Raucamp - http://www.cavey.nl
>
|||the in republishing column refers to a replcation topology where your
subscriber also servers as a publisher to other subscribers.
So you pubish the pubs database on your publisher to the pubs database on
the subscriber. This subscriber then publishes the same tables to other
subscribers.
"Gerald Raucamp" <caveyREMOVE@.THISvengeance.et.tudelft.nl> wrote in message
news:slrncshah0.o86.cavey@.vengeance.et.tudelft.nl. ..
> On 2004-12-21, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> Thanks for the links. I have checked out the articles but unfortunately
> they don't seem to apply to my situation.
> The identity ranges are adjusted on the publisher without much of a
> hickup using the sp_adjustpublisheridentityrange call. The problem lies
> at the subscriber side. Afaik. I'll give it a try though, I might be
> overlooking something.
> The second link concerning the insert trigger does not seem to be an
> issue either. I will check any triggers on the offending tables tomorrow
> for the "not for replication" directive. We had some troubles in the
> past (oversight is the correct word) with insert/update triggers firing
> when the Merge agent was updating info...
> I was wondering though what the "in_republishing" column means in the
> sp_MShelp_identityrange call. Does that mean the table/article is being
> republished on the subscriber side? If so, that would mean something is
> a miss, since currently there is only one publisher and one subscriber
> (more might be added in the future, depends on available IT
> infrastructures at the various branche offices).
> I'll also add the second step to my merge agent and will try to get more
> verbosity in the agent log file.
>
> --
> Gerald Raucamp - cavey@.vengeance.et.tudelft.nl
> http://www.cavey.nl
|||I think you incorrectly sized your ranges. You have to size your range to be
greater than the maximum batch update that your publisher/subscriber will
ever have between merge agent runs. So if you are offline for 1 hour, you
need to ensure that your range will not be exceeded by any update within
that one hour. You have to look at how many rows are modified by a single
update, and how many times an update runs.
What probably happened is a range was exceeded and you got the range
exceeded message, then the merge agent run and adjusted the range, and now
you have a new range assigned on the subscriber.
"Gerald Raucamp" <caveySPAM@.BEGONEvengeance.et.tudelft.nl> wrote in message
news:slrncsil9s.o86.cavey@.vengeance.et.tudelft.nl. ..
> Ok,
> I feel like an idiot now. It seems to be working now, I have no idea
> what I have done to make it work, but at least the subscriber is
> accepting inserts again.
> I was put off track by an error message generated by the application on
> the subscriber side. Past experience told us it had to do with the
> identity range being used up, after fixing the ranges the problem still
> persisted so I kept on looking within the database, while in fact the
> problem may now be at the application side (xsl/xml transformations).
> I would like to thank everybody for their time and I do hope the
> database issue has been resolved now. I'm just feeling a bit *hrmz hrmz*
> right now about the situation.
>
> --
> Gerald Raucamp - http://www.cavey.nl