Rebuilding Identity Seeds

Ok I'm sorry for all the questions. But we had a replication that was deleted
by accident and I've been trying to rebuild it and I'm getting all sorts of
problems.
Currently I've rebuild an anonymous merge replication for SQL CE devices.
I've setup 100,000 identity ranges so they will not have to renew often and
collide.
For some reason when I insert on the handheld it says can't insert a
duplicate identity field etc etc.
I think replication somehow screwed up the ranges for each handheld perhaps
because the old replication was deleted and still settings lingering I don't
know. I need someone to help me understand whats going on.
Basically I have a table right now that the current max through query
analyzer reports: 6700324
But when I execute this:
DBCC CHECKIDENT ('table', NORESEED)
I get:
Checking identity information: current identity value '70338', current
column value '6700324'.
Does that mean that all my handhelds got ranges 100,000 starting at 70338?
No wonder I'm getting duplicate keys right?
Further investigation in the MSrepl_identity_range table shows 3 lines like
this:
objid: 597577167
next_seed: 500000
pub_range: 100000
range: 100000
max_identity: 2147483647
threshold: 80
current_max: 500000
objid: 949578421
next_seed: 200000
pub_range: 100000
range: 100000
max_identity: 2147483647
threshold: 80
current_max: 200000
objid: 1029578706
next_seed: 200000
pub_range: 100000
range: 100000
max_identity: 2147483647
threshold: 80
current_max: 200000
I'm not sure where to go from here. Do I have to somehow manually call a
stored procedure to bump/reseed these subscribers?
Please any help would be greatly appreciated.
Thanks in advance.
Given the last two lines there it looks like two of your tables have started
to use the same identity range.
DBCC CHECKIDENT (TableName ) should reset the identiy fields. So will
truncating the table but obv at a fairly major cost!
"Synced" wrote:

> Ok I'm sorry for all the questions. But we had a replication that was deleted
> by accident and I've been trying to rebuild it and I'm getting all sorts of
> problems.
> Currently I've rebuild an anonymous merge replication for SQL CE devices.
> I've setup 100,000 identity ranges so they will not have to renew often and
> collide.
> For some reason when I insert on the handheld it says can't insert a
> duplicate identity field etc etc.
> I think replication somehow screwed up the ranges for each handheld perhaps
> because the old replication was deleted and still settings lingering I don't
> know. I need someone to help me understand whats going on.
> Basically I have a table right now that the current max through query
> analyzer reports: 6700324
> But when I execute this:
> DBCC CHECKIDENT ('table', NORESEED)
> I get:
> Checking identity information: current identity value '70338', current
> column value '6700324'.
> Does that mean that all my handhelds got ranges 100,000 starting at 70338?
> No wonder I'm getting duplicate keys right?
> Further investigation in the MSrepl_identity_range table shows 3 lines like
> this:
> objid: 597577167
> next_seed: 500000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 500000
> objid: 949578421
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> objid: 1029578706
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> I'm not sure where to go from here. Do I have to somehow manually call a
> stored procedure to bump/reseed these subscribers?
> Please any help would be greatly appreciated.
> Thanks in advance.
>
|||Well the ranges being the same on the last 2 are fine since their 2 seperate
tables and 2 seperate identity fields, so should have no problem sharing
ranges as their not related at all.
Could someone please comment. I've gotten further as I've gotten the insert
to work after doing a DBCC CHECKIDENT ('table', RESEED)
But the problem is when I inserted a new record, the ID was very low
compared to my highest ID. Should this be happening? I don't think
replication is smart enough to find out there was an empty chunk lower in the
ID fields and gave a handheld this range. In my experience in the past it
just always gave it a new range up top and the ID's escalate fast if your
redoing the publication alot.
Does this mean I'm eventually going to collide again once the numbers raise
because it gave my handheld a low range?
Please any help would be greatly appreciated. I'm stuck in a jam.
"Synced" wrote:

> Ok I'm sorry for all the questions. But we had a replication that was deleted
> by accident and I've been trying to rebuild it and I'm getting all sorts of
> problems.
> Currently I've rebuild an anonymous merge replication for SQL CE devices.
> I've setup 100,000 identity ranges so they will not have to renew often and
> collide.
> For some reason when I insert on the handheld it says can't insert a
> duplicate identity field etc etc.
> I think replication somehow screwed up the ranges for each handheld perhaps
> because the old replication was deleted and still settings lingering I don't
> know. I need someone to help me understand whats going on.
> Basically I have a table right now that the current max through query
> analyzer reports: 6700324
> But when I execute this:
> DBCC CHECKIDENT ('table', NORESEED)
> I get:
> Checking identity information: current identity value '70338', current
> column value '6700324'.
> Does that mean that all my handhelds got ranges 100,000 starting at 70338?
> No wonder I'm getting duplicate keys right?
> Further investigation in the MSrepl_identity_range table shows 3 lines like
> this:
> objid: 597577167
> next_seed: 500000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 500000
> objid: 949578421
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> objid: 1029578706
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> I'm not sure where to go from here. Do I have to somehow manually call a
> stored procedure to bump/reseed these subscribers?
> Please any help would be greatly appreciated.
> Thanks in advance.
>
|||Sorry, mis-read the question. The actual value is 6700324 and the identity
value is 70338. Right.
Are there any gaps in the actual figures? i.e. Are there really 6.7 million
records in the table or not?
Your choice seems to be that you need to renumber the field to get rid of
any gaps.
First of all, get rid of the identity property on the field. Then use the
following code :
declare @.intCounter int
set @.intCounter = 0
update myTable
SET @.intCounter = myTableField = @.intCounter + 1
The field will then have an incremented value for a value, you can then
stick the identity value back on. That should reset it for you.
MCT MCDBA MCSE MCSD MC yadda yadda yadda. Oracle Certified Associate
Remarc Technologies Ltd
Officially Number 1 for SQL 2005 in EMEA.
"Synced" wrote:
[vbcol=seagreen]
> Well the ranges being the same on the last 2 are fine since their 2 seperate
> tables and 2 seperate identity fields, so should have no problem sharing
> ranges as their not related at all.
> Could someone please comment. I've gotten further as I've gotten the insert
> to work after doing a DBCC CHECKIDENT ('table', RESEED)
> But the problem is when I inserted a new record, the ID was very low
> compared to my highest ID. Should this be happening? I don't think
> replication is smart enough to find out there was an empty chunk lower in the
> ID fields and gave a handheld this range. In my experience in the past it
> just always gave it a new range up top and the ID's escalate fast if your
> redoing the publication alot.
> Does this mean I'm eventually going to collide again once the numbers raise
> because it gave my handheld a low range?
> Please any help would be greatly appreciated. I'm stuck in a jam.
>
> "Synced" wrote:
|||Ok well I think your still misunderstanding the problem. The problem is with
the identity ranging allocation. Nothing to do with the current data.
It doesn't matter to us if the ID's have gaps. Basically the replication is
allocating ranges that are either going backwards, or already used ID's
instead of going forwards.
My questions are based on the replication not the actual ID field itself.
So I would hugely greatly appreciate some help from someone who has done
merge replication with identity ranges and knows this stuff. Please I would
be very greatful as I want to understand what is wrong and how to fix it.
Thanks in advance.
"dmdavies" wrote:
[vbcol=seagreen]
> Sorry, mis-read the question. The actual value is 6700324 and the identity
> value is 70338. Right.
> Are there any gaps in the actual figures? i.e. Are there really 6.7 million
> records in the table or not?
> Your choice seems to be that you need to renumber the field to get rid of
> any gaps.
> First of all, get rid of the identity property on the field. Then use the
> following code :
> declare @.intCounter int
> set @.intCounter = 0
> update myTable
> SET @.intCounter = myTableField = @.intCounter + 1
> The field will then have an incremented value for a value, you can then
> stick the identity value back on. That should reset it for you.
> --
> MCT MCDBA MCSE MCSD MC yadda yadda yadda. Oracle Certified Associate
> Remarc Technologies Ltd
> Officially Number 1 for SQL 2005 in EMEA.
>
> "Synced" wrote: