I'm having the same problem as below.
Briefly explained:
The distribution cleanup is taking longer and longer, which makes the logreader and snapshot agents are timed out.
---------------------------------------------
The problem...
After approx 1 week running replication my logreader agents on both publishers timed out & failed. I discovered that the distribution cleanup was taking longer & longer to complete. The last successful cleanup took 21 hrs on pub/dist 1, and 38 hrs on pub/dist
2. After that, the next run had run for more than 24 hrs each when I cancelled & disabled the task at each pub/dist. This allowed the logreaders to be restarted
and continue normally.
With SQL Profiler and a little experimentation, I traced the problem to the
sp_MSdelete_publisherdb_trans proc. The cleanup stalls on line 198:
delete MSrepl_transactions WITH (PAGLOCK) from MSrepl_transactions where
publisher_database_id = @publisher_database_id and xact_seqno <= @max_xact_seqno and
xact_seqno <> @last_xact_seqno and xact_seqno <> @last_log_xact_seqno and -- use
nolock to avoid deadlock not exists (select * from MSrepl_commands c (nolock) where
c.publisher_database_id = @publisher_database_id and
d.xact_seqno = MSrepl_transactions.xact_seqno)
Suspecting a bad plan, I recompiled, which was no help. I added a few lines to write
the param values to the errorlog, and manually ran the above statement (with set
rowcount 5000 to match the SP) in query analyzer in a transaction with a rollback to
keep from breaking anything.
It runs from query analyzer in less than two seconds, but takes an eternity from the
stored proc. I've tried running the sp_MSdistribution_cleanup proc directly from
query analyzer in case the sqlagent instance was bad, but I get the same results.
The profiler trace shows that in the minutes following where it appears to hang, it
is scanning msrepl_commands, yet the execution plan indicates a clustered index seek.
---------------------------------------------
Any ideas or anyone who had similar problems?
Thanks, & sorry for the long post.
Briefly explained:
The distribution cleanup is taking longer and longer, which makes the logreader and snapshot agents are timed out.
---------------------------------------------
The problem...
After approx 1 week running replication my logreader agents on both publishers timed out & failed. I discovered that the distribution cleanup was taking longer & longer to complete. The last successful cleanup took 21 hrs on pub/dist 1, and 38 hrs on pub/dist
2. After that, the next run had run for more than 24 hrs each when I cancelled & disabled the task at each pub/dist. This allowed the logreaders to be restarted
and continue normally.
With SQL Profiler and a little experimentation, I traced the problem to the
sp_MSdelete_publisherdb_trans proc. The cleanup stalls on line 198:
delete MSrepl_transactions WITH (PAGLOCK) from MSrepl_transactions where
publisher_database_id = @publisher_database_id and xact_seqno <= @max_xact_seqno and
xact_seqno <> @last_xact_seqno and xact_seqno <> @last_log_xact_seqno and -- use
nolock to avoid deadlock not exists (select * from MSrepl_commands c (nolock) where
c.publisher_database_id = @publisher_database_id and
d.xact_seqno = MSrepl_transactions.xact_seqno)
Suspecting a bad plan, I recompiled, which was no help. I added a few lines to write
the param values to the errorlog, and manually ran the above statement (with set
rowcount 5000 to match the SP) in query analyzer in a transaction with a rollback to
keep from breaking anything.
It runs from query analyzer in less than two seconds, but takes an eternity from the
stored proc. I've tried running the sp_MSdistribution_cleanup proc directly from
query analyzer in case the sqlagent instance was bad, but I get the same results.
The profiler trace shows that in the minutes following where it appears to hang, it
is scanning msrepl_commands, yet the execution plan indicates a clustered index seek.
---------------------------------------------
Any ideas or anyone who had similar problems?
Thanks, & sorry for the long post.
