Distribution Agent error

I use SQLServer2K to manage a sortation system. I have
one server that acts as the primary server and a backup
server that subscribes to the publication on the primary.
Lately, my distribution agent has been failing with the
following comment as the Last Action:
"The row was not found at the Subscriber when applying
the replicated command."
The time stamp shows this to occur at the beginning of
the work day. The last command run is
'CALL sp_MSdel_tblData (1920859)' where tblData is one of
the articles in a transactional publication. I also note
the Transaction Log start to pile up.
To clean things up, I wait til the end of the work day
(when replication should be idle) and do the following
procedure in this order:
1) Run the sp_ReplDone stored procedure.
(EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1)
2) Reinitialize subscriptions.
3) Run the snapshot agent.
4) Restart the distribution agent.
5) Backup the Transaction Log file.
6) Shrink the Transaction Log file.
USE Database
GO
DBCC SHRINKFILE (Database_Log, 1)
GO
Everything seems to run fine until the next morning and
the dist agent fails again. Is there something I'm
lacking? or am I doing something out of sequence?
Roger.
When you get this problem it means that the distribution agent is trying to
delete a row on the subscriber that does not exist there.
The first thing you have to do is identify why you have a database
consistency problem between your publisher and subscriber. You should run a
validation to determine how out of sync you are and then figure out why your
subscriber is out of sync. Are there any ohter processes writing to the
subscriber? Any users incorrectly pointing to this database instead of teh
publisher. You might want to run profiler to identify them.
If you like to live dangerously you can use the continue on data consistency
errors profile of the distribution agent, but then you will have no idea how
out of sync your subsciber is, or whether it is getting progressively out of
sync, or getting more in sync.
Your Transaction log should not be "filling up". You should be dumping it to
prevent the log from getting too large. For VLDBs dump every minute. Run the
full recovery model for best results, although you can run the simple
recovery model if you simply don't care about point in time recovery.
run a dbcc opentran to see if there are any open transactions and if you can
kill them.
if there is a value for the oldest non distributed transaction, your log
reader is getting behind or stopped.
While your strategy does work, it is not the best approach for this problem.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Roger Denison" <anonymous@.discussions.microsoft.com> wrote in message
news:243601c49bfc$47b93340$a601280a@.phx.gbl...
> I use SQLServer2K to manage a sortation system. I have
> one server that acts as the primary server and a backup
> server that subscribes to the publication on the primary.
> Lately, my distribution agent has been failing with the
> following comment as the Last Action:
> "The row was not found at the Subscriber when applying
> the replicated command."
> The time stamp shows this to occur at the beginning of
> the work day. The last command run is
> 'CALL sp_MSdel_tblData (1920859)' where tblData is one of
> the articles in a transactional publication. I also note
> the Transaction Log start to pile up.
> To clean things up, I wait til the end of the work day
> (when replication should be idle) and do the following
> procedure in this order:
> 1) Run the sp_ReplDone stored procedure.
> (EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1)
> 2) Reinitialize subscriptions.
> 3) Run the snapshot agent.
> 4) Restart the distribution agent.
> 5) Backup the Transaction Log file.
> 6) Shrink the Transaction Log file.
> USE Database
> GO
> DBCC SHRINKFILE (Database_Log, 1)
> GO
> Everything seems to run fine until the next morning and
> the dist agent fails again. Is there something I'm
> lacking? or am I doing something out of sequence?
> Roger.