Log Reader Agent Blocking

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Log Reader Agent Blocking

Log Reader Agent Blocking

Rate Topic

Display Mode

Topic Options

Author

Message

The Natrix

Posted Friday, July 16, 2010 11:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 8:54 AM
Points: 45, Visits: 571

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!

Post #954039

Chris Conner-455865

Posted Tuesday, April 19, 2011 6:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 11:10 AM
Points: 9, Visits: 218

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

Post #1095575

The Natrix

Posted Tuesday, April 19, 2011 8:28 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 8:54 AM
Points: 45, Visits: 571

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!

Post #1095675

Chris Conner-455865

Posted Tuesday, April 19, 2011 8:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 11:10 AM
Points: 9, Visits: 218

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?

Post #1095702

The Natrix

Posted Tuesday, April 19, 2011 9:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 8:54 AM
Points: 45, Visits: 571

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

Post #1095723

Chris Conner-455865

Posted Monday, April 25, 2011 7:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 11:10 AM
Points: 9, Visits: 218

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!

Post #1098100

The Natrix

Posted Monday, April 25, 2011 12:01 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 8:54 AM
Points: 45, Visits: 571

No problem, glad to hear it got fixed.

Post #1098224

superdanman84

Posted Monday, September 17, 2012 6:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 11:30 AM
Points: 1, Visits: 35

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

Post #1360166

« Prev Topic | Next Topic »

Permissions