July 16, 2010 at 11:20 am
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!
April 19, 2011 at 6:43 am
Did you ever get this resolved? Looks like I'm having a similar issue.
April 19, 2011 at 8:28 am
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!
April 19, 2011 at 8:50 am
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?
April 19, 2011 at 9:08 am
No we don't have a setup like that. We have transactional replication to 3 servers with 2 push and 1 pull subscription.
April 25, 2011 at 7:39 am
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!
April 25, 2011 at 12:01 pm
No problem, glad to hear it got fixed.
September 17, 2012 at 6:49 am
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
October 24, 2013 at 4:37 am
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