Log reader agent running but showing 0 cmds/sec

• Andreww

  Old Hand

  Points: 305

  More actions

  March 8, 2007 at 8:10 am

  #98179

  We did a number of deletion transactions on a (transactionally) replicated SQL 2000 database (replication has been running successfully for several years). Some of the transactions have removed millions of rows. At first the transactions were successfully replicated but at some stage this process seemed to have got stuck. The log reader agent claims to be running but is showing a delivery rate of 0 cmds/sec.

  The distribution agent is running and is showing "no replicated transactions are available".

  I suspect that the Log reader agent or the distribution database is choking on a particularly large transaction.

  I have tried querying the distribution database to see if the log reader agent is really adding commands or not, eg with SELECT COUNT(*) FROM msrepl_commands but after 15 minutes this has not returned a result.

• Darren Blizzard-417817

  Newbie

  Points: 6

  More actions

  March 18, 2007 at 9:23 pm

  #695629

  Hi Newbie,

  Check what Service Pack you are running.  If it is not SP4 then you will need to install it.

  SQL replication prior to this has trouble with really large transcations. I have recently had this issue with a large transactional replication.

  Check this out for more information - it might not be the same issue as yours but it sounds like it could be:

  http://support.microsoft.com/kb/884554

  I hope this helps.

  Darren