BcpBatchSize problem in replication

I have transactional replication setup between 2 sql 2000 sp3a servers
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
RonAny ideas on this? We are still experiencing the problem.
Thanks,
Ron
Ron <rgellenbecker@.doralsys.com> wrote in
news:Xns95DC57BC7F324doral39312565@.207.46.248.16:

> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The
> servers are running windows 2003. Everything is enterprise edition
> and the sql servers are using about 14 gig memory.
> We restore the database to the second server and then enable
> publication to that server turning of the generation of SPs but
> enabling the use of column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just
> fine. (before we started using column names).
> Up until this past weekend we did not need the column name option in
> the replication distribution and everything ran fine. Some table
> structures changed but not that big of changes but we do not have
> about 60 ident columns that we are dealing with.
> Starting this Monday we needed to use the column name option because
> we are replicating to some large tables that have identity columns on
> them. We have enabled the not for replication property on the
> columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it
> a couple of times and each time the distribution job ran fine for a
> while and then would just hang. Once it would hang there was no
> getting past that point.
> I have traced the replication process and it just stops sending data.
> I have also enabled the output of the distribution job to a file and
> it shows more data being sent than I see in the sql trace. The trace
> is not missing data because it always stops at the same spot (I tried
> it several times) and the output file always stops also but shows alot
> more data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the
> way down to 1 or 2 but the trace still shows many more commands per
> batch than 1 or 2. I have enabled the tsql batch start and end to
> trace the batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables
> with large rows I am thinking that the 10,000 commands per batch is
> too much now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
>