Log Reader Agent Blocking

  • Hey all,

    I have a push transaction replication on SQL Server 2008 that has the Log Reader Agent trying to execute sp_MSadd_replcmds that blocks other commands, which has resulted in replication stopping. Each time I kill the process and restart the Log Reader Agent that same process blocks everything else.

    When I view details on the Log Reader Agent through Replication Monitor the command count in 'Delivering replicated transactions, xact count:25, command count: 122701.' keeps getting higher and higher but no updates are made on the replicated database.

    Can anyone give me suggestions or clues as to why this is happening and how I can go about fixing it? Thanks in advance!

  • Did you ever get this resolved? Looks like I'm having a similar issue.

  • We were able to resolve the issue, but I can't say for sure which one thing fixed it. What I did was remove everything including the distribution database and started from scratch. After which I made a new log reader and snapshot agent (because I also had problems generating the snapshot). We have a large volume of transactions that are replicated so for my log reader agent I set ReadBatchSize to 1000 instead of 500. You can get to all the agents' properties by right clicking Replication and selecting Distributor Properties then Profile Defaults. Hope this helps!

  • Thanks for the info...

    I'll give it a shot.

    We have multiple subscriptions updating the same database (transactional and snapshot). Wondering if this could be part of the problem. Are you doing anything like that?

  • No we don't have a setup like that. We have transactional replication to 3 servers with 2 push and 1 pull subscription.

  • Found our issue...we had a bad query updating the transactional data which was causing locks. This was blocking the replication agents and then all sorts of bad stuff started happening. Thanks for your help!

  • No problem, glad to hear it got fixed.

  • Hi there,

    I am currently running into these same issues. However the distributor is an Oracle Database. I am not quite sure how to troubleshoot this from the Oracle side though. Any advice or where to look would be appreciated. I was thinking about starting from scratch as well but I definitely need to brush up on my Oracle documentation first.

    Thanks,

    Dan

  • Hi There,

    I had a similiar issues in one of our production database.

    Replication stopped working after a heavy purge of 15 million records was exexcuted at an off peak hour.

    Log reader agent showed 'delivering replicated transactions xact count 1 command count xxxxxx' for many hours.

    Well the issue was automatically resolved.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply