Replication-Replication Transaction-Log Reader Subsystem: agent XXXXXXXXXXXX failed. The last step did not log any message!

Nagarjuna Reddy 0 Reputation points
2024-03-11T13:07:01.44+00:00

Hi

--We have recently configured Transactional replication between 2 databases in 2 different servers

--From SQL longs we have noticed below error

Replication-Replication Transaction-Log Reader Subsystem: agent XXXXXXXXXXXX failed. The last step did not log any message!

Can you help us on this ??

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,447 questions
{count} votes

2 answers

Sort by: Most helpful
  1. LucyChen-MSFT 5,060 Reputation points Microsoft Vendor
    2024-03-12T07:36:08.68+00:00

    Hi @Nagarjuna Reddy,

    Thanks for the error messages you provided. You can follow the steps below to troubleshoot and solve your errors:

    1. The error 'No replicated transactions are available.' states that your subscription is uninitialized. You need to create the initial snapshot and verify the Log Reader Agent is running. You can try to drop the subscription and try again. Ensure the subscription has all of the required permissions in Replication Agent Security Model.
    2. From the error 'The process could not execute 'sp_replcmds' on 'XXXXXX'.', I think it could be possible that owner of the database could be someone other than what you have permissions for. If this method does not work, you can try another method according to the troubleshooting method in this link: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/replication/error-replication-log-reader-agent-fails
    3. For the error ‘ TCP Provider: An existing connection was forcibly closed by the remote host.’, please gather the following information:
    • The text of the error message and the error codes. Check whether the error is intermittent or consistent.
    • Application and system event logs from SQL Server and client system. These logs can help check if there are any system wide issues occurring on your SQL Server.
    • If the connections are failing from an application, collect the connection strings from the application. These strings are typically found in Web.config files for ASP.NET applications.
    • Collect and review SQL Server error logs for other error messages and exceptions.
    • If you have administrator access to the SQL Server computer, gather and review current computer settings and service accounts by using the following procedure:
      1. Download the latest version of SQL Check from the https://github.com/microsoft/CSS_SQL_Networking_Tools/wiki
      2. Unzip the downloaded file into a folder.
      3. Run the command prompt as an administrator to collect the data and save to a file.
      If you have access to the output of the SQL Check tool and review information in various sections in the output file, please use the information ‘Diffie-Hellman cipher suites are enabled’ to search for, possible risk of intermittent TLS failures if the algorithm version is different between clients and servers. Hope this article  can help you : https://learn.microsoft.com/en-us/troubleshoot/windows-server/certificates-and-public-key-infrastructure-pki/apps-forcibly-closed-tls-connection-errors and these examples may help you to troubleshoot: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/tls-exist-connection-closed .

    Please feel free to share with us any problems you encounter that cannot be resolved.

    Best regards,

    Lucy Chen


    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.

    https://docs.microsoft.com/en-us/answers/support/email-notifications

    1 person found this answer helpful.
    0 comments No comments

  2. Zahid Butt 956 Reputation points
    2024-03-11T14:02:50.86+00:00

    Hi Nagarjuna Reddy,

    In SSMS object explorer, on the target instance under Jobs you will find replication jobs.

    Select the log reader job , right click on it, select view history and try to read proper error message.

    Hopefully you may find the root cause or put that error message here.

    Regards,


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.