Identity Range not working for master

I have set up a publisher database and a subscriber database which is a replica of the publisher. For the identity fields I set it up so that they would have a range of 10 numbers with an 80% margin. I was testing this on the subscriber and replica at t
he same time. I disconnected the subscriber, used up 8 id's, then connected and sure enough it gave me a new range. However whilst using the publisher it used up all 10 in the range and then gave me an error message! Surely it should have automatically
given me a new range once I'd hit 80% of the previous range.
Any help?
Thanks
Adrian
that depends on how large the batch is. So if you update 20 records in a
batch, it won't get updated and you blow the range.
The idea is to pick ranges that are much larger than representative batches.
So if I were you, I'd try ranges in the 1000's or set ranges that will not
be exceeded in the life time of your replication solution.
Hilary
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> I have set up a publisher database and a subscriber database which is a
replica of the publisher. For the identity fields I set it up so that they
would have a range of 10 numbers with an 80% margin. I was testing this on
the subscriber and replica at the same time. I disconnected the subscriber,
used up 8 id's, then connected and sure enough it gave me a new range.
However whilst using the publisher it used up all 10 in the range and then
gave me an error message! Surely it should have automatically given me a
new range once I'd hit 80% of the previous range.
> Any help?
> Thanks
> Adrian
|||Adrian,
as well as Hilary's reply, you could also consider manual range management
and use an algorithm that ensures no overlap in the ranges:
http://www.mssqlserver.com/replicati...h_identity.asp
HTH,
Paul Ibison
|||Hilary
I do intend to use much larger ranges, however I was just testing out the process on a smaller range to see it in action. It worked for the subscriber but not the publisher?
Any thoughts?
Regards
Adrian
"Hilary Cotter" wrote:

> that depends on how large the batch is. So if you update 20 records in a
> batch, it won't get updated and you blow the range.
> The idea is to pick ranges that are much larger than representative batches.
> So if I were you, I'd try ranges in the 1000's or set ranges that will not
> be exceeded in the life time of your replication solution.
> Hilary
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> replica of the publisher. For the identity fields I set it up so that they
> would have a range of 10 numbers with an 80% margin. I was testing this on
> the subscriber and replica at the same time. I disconnected the subscriber,
> used up 8 id's, then connected and sure enough it gave me a new range.
> However whilst using the publisher it used up all 10 in the range and then
> gave me an error message! Surely it should have automatically given me a
> new range once I'd hit 80% of the previous range.
>
>
|||There are a couple of issues here
1) are you running your agent continuously? Running is on a schedule,
every 5-10 minutes, or even less can help the adjustment, otherwise you
might want to manually execute the increment procedure
(sp_adjustpublisheridentityrange) to adjust everything
2) its not clear to me that the number of rows in the batch was close
enough to the threshold to kick off the indentity range adjustment. Was it?
3) The allottment of ranges is not always intuitive. For instance if I
set a range on the Publisher of 100, the Publisher may "own" 0-200, where
the Subscriber "owns" 200-300. You have to look at the check constaints on
the indentity range tables to figure this out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:308EF6B0-3714-4D95-8BE6-4EBD352B1929@.microsoft.com...
> Hilary
> I do intend to use much larger ranges, however I was just testing out the
process on a smaller range to see it in action. It worked for the
subscriber but not the publisher?[vbcol=seagreen]
> Any thoughts?
> Regards
> Adrian
> "Hilary Cotter" wrote:
batches.[vbcol=seagreen]
not[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
on[vbcol=seagreen]
subscriber,[vbcol=seagreen]
then[vbcol=seagreen]
a[vbcol=seagreen]