Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log Reader Agent Blocking Expand / Collapse
Author
Message
Posted Friday, July 16, 2010 11:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:40 AM
Points: 45, Visits: 611
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
Posted Tuesday, April 19, 2011 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 19, 2011 8:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:40 AM
Points: 45, Visits: 611
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
Posted Tuesday, April 19, 2011 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 19, 2011 9:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:40 AM
Points: 45, Visits: 611
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
Posted Monday, April 25, 2011 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, April 25, 2011 12:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:40 AM
Points: 45, Visits: 611
No problem, glad to hear it got fixed.
Post #1098224
Posted Monday, September 17, 2012 6:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 10:52 AM
Points: 1, Visits: 39
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
Posted Thursday, October 24, 2013 4:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 23, 2013 2:31 AM
Points: 84, Visits: 226
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.
Post #1507961
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse