RE: running out of ranges for identity.
From: SQLGal (SQLGal_at_discussions.microsoft.com)
Date: 08/17/04
- Next message: RS: "Log shipping not work, but jobs say successful"
- Previous message: Paul Ibison: "Re: The issue of "Transactional Replication" in SQL 2000 Replicati"
- Next in thread: Hilary Cotter: "Re: running out of ranges for identity."
- Reply: Hilary Cotter: "Re: running out of ranges for identity."
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 17 Aug 2004 03:19:02 -0700
I hope someone out there can help - I have a similiar issue:
Here are the facts:
I have a Publisher in the UK and Subscribers in UK, France, Germany, US,
Australia and Singapore.
Distributor is on another SQL Server box which is also the UK Subscriber.
It's a 3GB database which is in constant use.
Some of the tables have Identity columns (including CONTACT_COMMS) and I
have set them all up in the publication to have automatic allocation of ID
Ranges.
Replication is working fine, but it is refusing to allocate a new range to
one of our tables (CONTACT_COMMS) on the publisher. We get the usual
'Identity range is full and must be updated by a replication agent'.
The merge agents have been configured to run every minute (and always have
been), so stopping and starting the merge agent as per KB Article 304706 is
not going to help.
I have also tried running sp_adjustpublisheridentityrange stored procedure
with no luck. I have been running it on the publisher with the parameters of
table name ad owner ie
exec sp_adjustpublisheridentityrange @table_name = 'contact_comms',
@table_owner = 'dbo'
The stored procedure just says 'command completed successfully'. There is
no indication of 0 for success or 1 for failure. There is no alteration to
the constraints on the CONTACT_COMMS table, so the stored procedure is
obviously not working.
When I run the stored procedure at the distributor, I get the following error:
Server: Msg 21315, Level 16, State 1, Procedure
sp_adjustpublisheridentityrange, Line 60
Failed to adjust Publisher identity range for table 'contact_comms'.
When I look at the distribution database on the distributor, the
Srepl_identity_range table has a valid entry for next_seed.
I hope someone can help - I really would like an answer to this.
"Duncan" wrote:
> I am trying to run a script that is attempting to insert about 5000 rows
> into a table, approx 5000 into a second, and probably about 150000 into a
> 3rd table.
>
> All 3 tables are being replicated, and al 3 have identity ranges set around
> the 100,000,000 mark.
>
> Trouble is when i run the script to start inserting these items, I get the
> following error:
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]The identity range managed by
> replication is full and must be updated by a replication agent. The INSERT
> conflict occurred in database 'MPlus', table 'wwwCopy', column 'cpy_id'.
> Sp_adjustpublisheridentityrange can be called to get a new identity range.
>
> I am at the point where I am deleting everything in the tables, running
> Sp_adjustpublisheridentityrangeon all 3, then running the merge to make sure
> its all propogated and then trying the script again, only to find out that
> its a different table that is now throwing the same error.
>
> Can someone please help me resolve this? I really need to get this data
> across, but I am being held back by something that on the surface looks
> really simple.
>
> Thanks
>
> --
> Duncan
>
>
>
- Next message: RS: "Log shipping not work, but jobs say successful"
- Previous message: Paul Ibison: "Re: The issue of "Transactional Replication" in SQL 2000 Replicati"
- Next in thread: Hilary Cotter: "Re: running out of ranges for identity."
- Reply: Hilary Cotter: "Re: running out of ranges for identity."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
