replication-log reader, dbcc reindex and transaction log backup.

We are using SQL 2K standard with sp4 and Push replication. Let me explain
my situation here first.
We have a weekly dbcc reindex happen at 3:05 am on Sunday morning and
finished successfully at 3:42 am. The replication - log reader started
issuing error in the log reader agent history, "The process could not execute
'sp_replcmds' on serverA..." starting at 3:14 am and being recurred every 6
minutes until 3:44 am. At that time, this error became red and stopped. Our
hourly transaction backup job kicked in at 3:45 am and took 10 minutes to
finish and normally it would take less than 20 seconds to complete. Later in
the morning, someone reported that they don't see any changed data replicated
to the subscriber server and indeed it wasn't working. All these activities
were on the same server, publishing server.
I have some questions regarding replication in this siutation.
1. Will the changes make by dbcc reindex be replicated to the subscriber
server? If so, will this explain the situation described above. if this is
the case, what should I do to minimize the effect of dbcc reindex to
log-reader? It is hard to believe someone would get up at 3:00 am Sunday
morning and making data changes which triggered the transactional
replication.
2. The weekend prior, with the same setup except no transaction log
backup (at that time, the backup mode was still set to simple), we didn't
encounter the 'sp_replcmds' error in the log reader agent. Is there any
relationship between the transaction log backup proces and log reader process
or they run independ of each other?
3. Why does the 'could not execute sp_replcmds' error occur and how to cure
this problem? Until this is solved, replication is down.
4. Is there a way I can schedule the Log Reader agent to stop and start at
a specific time?
wingman
1. Replication moves insert, update, and delete statements. That is all.
No other operation is moved through the replication engine in SQL Server
2000. If you reindex, it only occurs locally. When you reindex, you write
a huge volume of changes into the transaction log which does impact the
response time on the log reader since it has to scan through all of that
data. A change doesn't trigger replication. Replication constantly polls
for changes which have occured and moves anything that it finds.
2. There is not relationship between the log reader and a transaction log
backup. Replication only picks up transactions which have been committed.
A transaction log backup will not remove a committed transaction from the
log until it has been successfully written to the distribution database.
So, the Log Reader can have an impact on the size of the transaction log,
because if the log reader isn't running or can't transfer transactions to
the distribution database for some reason, then the tran log continues to
grow.
3. Is the error continuing to be thrown or was it a transient issue occuring
during the reindex operation?
4. The Log Reader agent is just a job. You can start it and stop it
whenever you want. You can even create a job to stop and start it at
particular times.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:555110A9-6925-4308-8572-F5E2653521AA@.microsoft.com...
> We are using SQL 2K standard with sp4 and Push replication. Let me
> explain
> my situation here first.
> We have a weekly dbcc reindex happen at 3:05 am on Sunday morning and
> finished successfully at 3:42 am. The replication - log reader started
> issuing error in the log reader agent history, "The process could not
> execute
> 'sp_replcmds' on serverA..." starting at 3:14 am and being recurred every
> 6
> minutes until 3:44 am. At that time, this error became red and stopped.
> Our
> hourly transaction backup job kicked in at 3:45 am and took 10 minutes to
> finish and normally it would take less than 20 seconds to complete. Later
> in
> the morning, someone reported that they don't see any changed data
> replicated
> to the subscriber server and indeed it wasn't working. All these
> activities
> were on the same server, publishing server.
> I have some questions regarding replication in this siutation.
> 1. Will the changes make by dbcc reindex be replicated to the subscriber
> server? If so, will this explain the situation described above. if this
> is
> the case, what should I do to minimize the effect of dbcc reindex to
> log-reader? It is hard to believe someone would get up at 3:00 am Sunday
> morning and making data changes which triggered the transactional
> replication.
> 2. The weekend prior, with the same setup except no transaction log
> backup (at that time, the backup mode was still set to simple), we didn't
> encounter the 'sp_replcmds' error in the log reader agent. Is there any
> relationship between the transaction log backup proces and log reader
> process
> or they run independ of each other?
> 3. Why does the 'could not execute sp_replcmds' error occur and how to
> cure
> this problem? Until this is solved, replication is down.
> 4. Is there a way I can schedule the Log Reader agent to stop and start
> at
> a specific time?
> wingman
>