Replication issue

aemhD 1 Reputation point
2021-11-15T13:23:20.423+00:00

I have a sudden replication issue.
I am working on SQL 2008R2 with Transactional replication with updated transactions, suddenly the data are not synced from the publisher to any subscriber.

I am receiving the following, while checking the replication monitor view, in the Log Reader Agent:
"The process is running and is waiting for a response from the server"
"the process could not execute sp_replcmds "

I have checked both:

  • Connection is Correct
  • Database security is OK

I have now more that 1 million record resulting from Sp_replcounters

Also, adding the "-Output "D:\VerboseLog\Log.txt" -Outputverboselevel 3" to the job properties doesn't show any additional info.

Swift reply will be appreciated.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,437 questions
0 comments No comments
{count} votes

7 answers

Sort by: Most helpful
  1. Soccan 16 Reputation points
    2021-11-15T14:28:48.313+00:00
    0 comments No comments

  2. Tom Phillips 17,741 Reputation points
    2021-11-15T21:28:31.357+00:00

    The simplest answer is to reinit the subscription using a snapshot.

    The answer to your question is likely it is timing out while running. This can be caused by many things. Including too many transactions to process at once. When you have lots of transactions, the default settings are not good enough to handle that and you need to tweak the agent settings. I would not do that unless it happens again, or you can't reinit using snapshot.

    If you cannot reinit, try changing CommitBatchSize to 5. This will reduce the number of commands requested to be applied to the subscriber. The agent must be restarted to take effect. I would also change from "continuous" mode to a schedule of every 1 min. That will create a log every time it runs, instead of never closing the log file. https://learn.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-distribution-agent?view=sql-server-ver15

    0 comments No comments

  3. Seeya Xi-MSFT 16,571 Reputation points
    2021-11-16T02:38:19.593+00:00

    Hi @aemhD ,

    This error occurs most often when you have a large transaction(s).
    To begin with, check if there are any open transactions with DBCC OPENTRAN() . Maybe you can get the message which shows Oldest LSN.
    Then run the following T-SQL statements to explore the transaction log:

    select  [Transaction ID], count([Transaction ID]) as 'Count log entries'  
    from::fn_dblog('YourOldest LSN',null)  
    where [Description] = 'REPLICATE'   
    group by [Transaction ID]   
    GO  
    

    Note: The fn_dblog() function accepts two parameters, Start LSN and End LSN.

    If there is a large number of logs records per given transaction, please increase the QueryTimeOut for the LogReader Agent Profile.
    For short-term monitoring, you can also set the Agent ReadBatchSize = 1 and VerboseHistory = 2 to get more feedback from the Agent.
    For more information, see the last part of this article which could give your more details.
    https://repltalk.com/2019/01/31/the-process-could-not-execute-sp_replcmds/

    Hope your problem could be solved!

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. aemhD 1 Reputation point
    2021-11-16T10:26:37.187+00:00

    Thanks everyone,
    the error seems to be passed after adjusting the QueryTimeOut, I was changing it from a wrong place.

    but I may need you help also with the current status, the log reader seems to be working with such info:

    "Delivering replicated transactions, xact count: 1 command count 47001"
    &
    "The log reader agent is scanning the transaction log for commands to be replicated. approximately 950000 log records have been scanned in pass #2, 0 of which were marked for replication "

    but actually, no thing is being replicated!
    I left it over 8 hours and no results, or replicated items.
    can any one guide me through.. I really can't reinitialize again.


  5. Seeya Xi-MSFT 16,571 Reputation points
    2021-11-18T09:23:59.063+00:00

    Hi @aemhD ,

    "The log reader agent is scanning the transaction log for commands to be replicated. approximately 950000 log records have been scanned in pass #2, 0 of which were marked for replication "

    Once the transaction gets committed by the application, the Log Reader Agent needs to scan those huge data changes performed on the Publisher database transaction logs. Thus, you can think of it as a warning.

    "Delivering replicated transactions, xact count: 1 command count 47001"

    Because the amount of data is too large and the transaction is huge, we may see such information.
    This will happen till the Log Reader Agent scans the complete set of changes and identifies that transaction was fully read from the Transactional Logs.

    For more information, please see this article. Several ways are post to resolve these problems.
    https://codingsight.com/sql-server-transactional-replication-issues/

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.