Out of sync

Hi,
We are using SQl 2000 EE with sp3a and doing transactional replication of
300+gb db.
frequently the subscribers are out of sync .
In order to spped up the replication process we have modfied the polling
interval to '1' and commit batch size to '1000' of distribution db ,Log
reader agent polling interval to 1 and Readbatchsize to 1000.
some times the subscriptions are in sync and sometimes not.
Why the log reader agent is not able to commit more than 100 transactions to
distribution db.Please suggest some thing in order to speed up the
replication process
I have executed sp_replcommands on OLtp it has nearly 75000 transactions to
commit.
Thanks
Murali
How big is your transaction log?
When you start to get big (and I don't mean your waist size) transactional
log maintenance is a must. You should be dumping your log as frequently as
each minute. You must also try to size your transaction log as well.
To do this observer the size of your tlog. Back it up, and shrink it. Then
size it to a best guess of what it should be.
Run dbcc loginfo to get an idea of your virtual log files. After a shrink
you should have 4. You want to run with perhaps 16-64, definitely not
several hundred or thousand. This will be an iterative process, but you
might start at 10 Mg, and work up/down from there.
The reason you want to maintain your transaction log is you want to minimize
the size of the log your log reader agent has to read. Also I would bump up
the read batch size threshold to 200 or more. If you run into problems with
your log reader being unable to chew through the log (the could not execute
sp_replcmds message) you will want to drop the ReadBatchSize and the
ReadBatchSizeThreshold values, but other than that you will want to increase
it.
Also set the HistoryVerboseLevel setting to 0.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Murali" <Murali@.discussions.microsoft.com> wrote in message
news:2D4B32EA-2860-4182-B914-FADECC2C9224@.microsoft.com...
> Hi,
> We are using SQl 2000 EE with sp3a and doing transactional replication of
> 300+gb db.
> frequently the subscribers are out of sync .
> In order to spped up the replication process we have modfied the polling
> interval to '1' and commit batch size to '1000' of distribution db ,Log
> reader agent polling interval to 1 and Readbatchsize to 1000.
> some times the subscriptions are in sync and sometimes not.
> Why the log reader agent is not able to commit more than 100 transactions
to
> distribution db.Please suggest some thing in order to speed up the
> replication process
> I have executed sp_replcommands on OLtp it has nearly 75000 transactions
to
> commit.
> Thanks
> Murali
>
|||oops, make sure HistoryVerboseLevel is 1 for the Log Reader Agent and 0 for
the Distribution Agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Murali" <Murali@.discussions.microsoft.com> wrote in message
news:2D4B32EA-2860-4182-B914-FADECC2C9224@.microsoft.com...
> Hi,
> We are using SQl 2000 EE with sp3a and doing transactional replication of
> 300+gb db.
> frequently the subscribers are out of sync .
> In order to spped up the replication process we have modfied the polling
> interval to '1' and commit batch size to '1000' of distribution db ,Log
> reader agent polling interval to 1 and Readbatchsize to 1000.
> some times the subscriptions are in sync and sometimes not.
> Why the log reader agent is not able to commit more than 100 transactions
to
> distribution db.Please suggest some thing in order to speed up the
> replication process
> I have executed sp_replcommands on OLtp it has nearly 75000 transactions
to
> commit.
> Thanks
> Murali
>