"Gerald Raucamp" <caveyREMOVE@THISvengeance.et.tudelft.nl> wrote in message
news:slrncsgete.o86.cavey@vengeance.et.tudelft.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

"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 not insert 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