Re: Sp_adjustpublisheridentityrange and resetting identity range
From: Pete Ocasio (pete.ocasio_at_execupay.com)
Date: 09/28/04
- Next message: RRN: "Re: Transactional Replication"
- Previous message: Hilary Cotter: "Re: MSrepl_commands question"
- In reply to: Hilary Cotter: "Re: Sp_adjustpublisheridentityrange and resetting identity range"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 28 Sep 2004 17:19:35 -0500
Thanks Hilary,
As Always a life saver... I will check your comments and follow the
instructions. I do remember making a change to the batch file size that
might be the reason for my problem. Again thanks for your assistance.
"Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:OCawPbZpEHA.868@TK2MSFTNGP10.phx.gbl...
> Lets review the problems one by one.
>
> The first problem could be related to one of two things. The first could
be
> that the constraint on the mpwWebReturnMsgs is left over from the old
> publication/subscription. Do an sp_help mpwWebReturnMsg on your subscriber
> and see if the constraint for your identity table matches the automatic
> identity ranges you have set on your publisher.
>
> The second aspect of the first problem is related to to how you configured
> automatic identity range management and the average size of your batch.
You
> have to set a range and a threshold which is well below the maximun size
of
> your batches.
>
> If your range is 100 and you update 1000 rows in a batch you will blow
your
> range every time. If your range is 1000 and you update 1000 rows in a
batch
> you will blow your range every time. If your range is 1000 and your
> threshold is 80% and you update 800 rows, you will probably blow your
range,
> if not in the first update, but in the second. If your range is 1000 and
> your threshold is 80% and you update 100 rows in a batch you will probably
> find that automatic identity range management will work well for you.
>
> Many dba's choose to set the ranges on their publishers and subscribers to
> ranges which will work for the lifetime of their replication solution.
This
> type of approach is called set it and forget it.
>
> Your second problem is somewhat more strange. The table on the publisher
> should fill in the guid value for the rowguid column, and the merge agent
> should apply this value on the subscriber.
>
> Something sounds very strange in your setup. Check out this script to
clean
> up your publisher and subscriber. Before you run this script out your
> publication and subscriptions. Drop them and then run the script.
>
>
http://groups.google.com/groups?selm=%230cs2ijlEHA.3016%40tk2msftngp13.phx.gbl&output=gplain
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "Pete Ocasio" <pete.ocasio@execupay.com> wrote in message
> news:%23AbaSlYpEHA.2588@TK2MSFTNGP12.phx.gbl...
> > Recently one of my remote sql servers crashed. This server was a
> publisher
> > that replicated 8 pubs to a subscriber. I reinstalled the sql server
and
> > restored the published database from a backup. I then proceeded to set
> the
> > replication to a new subscriber database. Eache time I try to run the
web
> > application associated with the subscriber database, the following
errors
> > come up:
> >
> > Unable to proceess request(1):[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
> > '100058N_SUB', table 'mpwWebReturnMsgs', column 'entry_id'.
> > Sp_adjustpublisheridentityrange can be called to get a new identity
range.
> >
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value
> NULL
> > into column 'rowguid', table '100058N_SUB.dbo.REPORTS'; column does not
> > allow nulls. INSERT fails.
> >
> > I tried the Sp_adjustpublisheridentityrange but it does nothing to
remedy
> > the situation nor do i know the reason for the second problem. I have
> over
> > thirty servers replicating to three subscribers. I am thinking of
> cleaning
> > the database in question of all replication settings and starting over
> > again.
> >
> > Does any of you knows the reason and/or solution to the problems above?
> >
> > Does any of you have a script that will clean up all the replication
> > settings in a database and sql server?
> >
> > Thanks for any help in advance.
> >
> >
>
>
- Next message: RRN: "Re: Transactional Replication"
- Previous message: Hilary Cotter: "Re: MSrepl_commands question"
- In reply to: Hilary Cotter: "Re: Sp_adjustpublisheridentityrange and resetting identity range"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
