Troubleshooting ‘The process could not execute ‘sp_repldone/sp_replcounters’

Share this Post

Chris Skorlinski, SQL Server Escalation Services

I worked on customer’s Transactional Replication issue today where LogReader was failing, restarting, then failing again.  We used link below to enable logging to get a clearer picture of failure.

16:02:45 OLE DB DISTOLE: sp_MSget_last_transaction
16:02:45 Publisher: {call sp_repldone ( 0x000a35e7000315e7005b, 0x000a35e7000315e7005b, 0, 0)}
16:32:45 Status: 2, code: 20011, text: ‘The process could not execute ‘sp_repldone/sp_replcounters’
16:32:45 The process could not execute ‘sp_repldone/sp_replcounters’

At first I was heading down the “Replication broken, you’ll need to start over path”, but looking later in the log I noticed the true failure, a query timeout.

16:32:45 Status: 2, code: 0, text: ‘Query timeout expired’.

Reviewing the time entries I confirmed the Log Reader called sp_repldone at 16:02 to validate the starting point in order to pull new transactions.  Then 30 minutes later at 16:32 we get the “query timeout expired”.  We added “-QueryTimeOut 7200” increasing Log Reader timeout to 2 hours then restarted the Agent.

16:53:38 Publisher: {call sp_repldone ( 0x000a35e7000315e7005b, 0x000a35e7000315e7005b, 0, 0)}
17:39:33 Publisher: {call sp_replcmds (100, 0, 0, , 16, 500000)}
17:39:35 Status: 16384, code: 20007, text: ‘No replicated transactions are available.’.

Now after 45 minutes Log Reader submitted sp_replcmds and began pulling down new transactions.   We confirmed, a recent index maintenance job had bloated the Transaction log to 200gb.  The Log Reader needed just a few more minutes to find the new starting point for pending transactions.  Once past the query timeout, within seconds, all pending transactions where distributed.

Share this Post

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.