1

I've got a nasty SQL Server 2012 replication issue. I have a transactional publication going to a pull subscriber. The log reader agent on the publisher has gotten stuck in a loop of:

Delivering replicated transactions, xcount: 1 with an increasing command count messages

and

The replication agent has not logged a progress message in 10 minutes

messages.

So the log reader agent seems to be working but nothing is being replicated to the distributors. All distributors state

The process is running and is waiting for a response

or they timeout and try again. When I run sp_whoisactive, I see the spid for the log reader agent for that database is blocking all the distribution agents from the subscribers of that database....yet the status of the log reader process is sleeping. The t-log for the publication database is growing fast and eating up storage. It's stuck with log_reuse_wait_desc=REPLICATION, so I cannot shrink it. Anyone run into this before? Any suggestions on how to fix this? Appreciate the help. Thanks.

  • Update: I've upped the query timeout for log reader agent to 42300 (12 hours) as suggested to me on twitter #sqlhelp. This helped, log reader now scanning Tlog continuosly. This is some progress but the scanning of the tlog is taking a very long time and still no data sent to the distro agents.! Hopefully once done it'll start sending data to the distro agents. I'm in wait & see mode now. I'm thinking my only resort will have to be a reinitialize. That is a truly last resort as our publication is huge. Open to tips too. – ManuelRdgz Mar 8 '18 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.