Identity Range not being updated

I have an SQL server 2000 environment where 1 server is the
publisher/database server and several clients using desktop edition sql
Server 2000. This environment is using merge replication. Several of my
tables utilize identity columns so I have my articles defined to
automatically manage identity ranges and I kept the default refresh rate at
80%. For users that are local for this application they use a web
application that connects to the database. Remote users use a windows
desktop application to connect to their subscriber database. When a remote
user returns to the office environment they are allowed to synchronize.
The problem I am encountering is that as users are connecting locally using
the web application to the server database, I had an error occur that stated
the identity range was full. The error told me what table/column that was
having a problem and that it had to be corrected by a replication agent. The
error also reported that I could us sp_adjustpublisheridentityrange to
correct the problem.
My question is why didn't the identity range automatically get corrected if
it was set at 80%?
Also, what is the best practice for how to handle this problem. My concern
is that this is a very heavily used system and how should I keep this error
from occurring?
i think sp_adjustpublisheridentityrange it's being called automatically
only when a subscriber connects and starts a merge job. if your
subscribers stay disconneted for a long time,
sp_adjustpublisheridentityrange won't be called, and you'll have to do
it manually
Guy Thornton ha scritto:

> I have an SQL server 2000 environment where 1 server is the
> publisher/database server and several clients using desktop edition sql
> Server 2000. This environment is using merge replication. Several of my
> tables utilize identity columns so I have my articles defined to
> automatically manage identity ranges and I kept the default refresh rate at
> 80%. For users that are local for this application they use a web
> application that connects to the database. Remote users use a windows
> desktop application to connect to their subscriber database. When a remote
> user returns to the office environment they are allowed to synchronize.
> The problem I am encountering is that as users are connecting locally using
> the web application to the server database, I had an error occur that stated
> the identity range was full. The error told me what table/column that was
> having a problem and that it had to be corrected by a replication agent. The
> error also reported that I could us sp_adjustpublisheridentityrange to
> correct the problem.
> My question is why didn't the identity range automatically get corrected if
> it was set at 80%?
> Also, what is the best practice for how to handle this problem. My concern
> is that this is a very heavily used system and how should I keep this error
> from occurring?
|||It only adjust the range if you sync within the threshold. You need to pick
representative thresholds which will allow the maximum number of expected
inserts within syncs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
news:327C95D4-2582-45E5-8E25-3EBACCF3C0FB@.microsoft.com...
>I have an SQL server 2000 environment where 1 server is the
> publisher/database server and several clients using desktop edition sql
> Server 2000. This environment is using merge replication. Several of my
> tables utilize identity columns so I have my articles defined to
> automatically manage identity ranges and I kept the default refresh rate
> at
> 80%. For users that are local for this application they use a web
> application that connects to the database. Remote users use a windows
> desktop application to connect to their subscriber database. When a
> remote
> user returns to the office environment they are allowed to synchronize.
> The problem I am encountering is that as users are connecting locally
> using
> the web application to the server database, I had an error occur that
> stated
> the identity range was full. The error told me what table/column that was
> having a problem and that it had to be corrected by a replication agent.
> The
> error also reported that I could us sp_adjustpublisheridentityrange to
> correct the problem.
> My question is why didn't the identity range automatically get corrected
> if
> it was set at 80%?
> Also, what is the best practice for how to handle this problem. My
> concern
> is that this is a very heavily used system and how should I keep this
> error
> from occurring?
|||Hilary,
Thanks for your reply. So is this to say that a user must synchronize
within the 80% threshold in order for it to be updated? If true that will be
hard for me to predict and set the threshold appropriately.
I wonder if it would be better for us to schedule a job to run periodically
to update the identity ranges? any ideas?
Thanks.
"Hilary Cotter" wrote:

> It only adjust the range if you sync within the threshold. You need to pick
> representative thresholds which will allow the maximum number of expected
> inserts within syncs.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
> news:327C95D4-2582-45E5-8E25-3EBACCF3C0FB@.microsoft.com...
>
>
|||No, the user must sync between 80% of the threshold and 100% of the
threshold. Should he/she sync less than 80% of the threshold the range would
not be incremented.
The best approach is to have them sync within this range or set large
ranges.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
news:CFADF7BC-E713-46AD-B45B-FA91AFDA41A1@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thanks for your reply. So is this to say that a user must synchronize
> within the 80% threshold in order for it to be updated? If true that will
> be
> hard for me to predict and set the threshold appropriately.
> I wonder if it would be better for us to schedule a job to run
> periodically
> to update the identity ranges? any ideas?
> Thanks.
> "Hilary Cotter" wrote:
|||The problem I am running into is that my identity ranges are filling up at
the publication server. Even if the user synchronizes within the threshold.
If my users are are not in the db_admin role then would that cause the ranges
not to get updated? If so, how can I work around that? My users cannot be
in the db_admin role.
"Hilary Cotter" wrote:

> No, the user must sync between 80% of the threshold and 100% of the
> threshold. Should he/she sync less than 80% of the threshold the range would
> not be incremented.
> The best approach is to have them sync within this range or set large
> ranges.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
> news:CFADF7BC-E713-46AD-B45B-FA91AFDA41A1@.microsoft.com...
>
>
|||Hi Guy
I have the same problem with the identity range on the publication server
filling up. Have you found a soloution to this yet?
The subscribers get updated when they sync but not the server. How should I
get the server to update?
Thank you
/ Henrik
"Guy Thornton" wrote:
[vbcol=seagreen]
> The problem I am running into is that my identity ranges are filling up at
> the publication server. Even if the user synchronizes within the threshold.
> If my users are are not in the db_admin role then would that cause the ranges
> not to get updated? If so, how can I work around that? My users cannot be
> in the db_admin role.
> "Hilary Cotter" wrote:
|||Hi Henrik,
To correct the problem, we had to adjust the size of our identity ranges to
account for how often our remote users would synchronize with the server.
Adjust the range so that you have enough at the server to accomodate the
number of inserts expected in between when users synchronize with the server.
We also had to add the users to the sysadmin fixed server role to
automatically update the ranges. Otherwise we would have had to setup an
automated job to run periodically to adjust the ranges.
My concern now is what will happen when my ranges run out. How do I
allocate more ranges over time to ensure continued operation of my
application.
"Henrik" wrote:
[vbcol=seagreen]
> Hi Guy
> I have the same problem with the identity range on the publication server
> filling up. Have you found a soloution to this yet?
> The subscribers get updated when they sync but not the server. How should I
> get the server to update?
> Thank you
> / Henrik
> "Guy Thornton" wrote: