none
SQL server 2016 Replication issue - The process could not execute 'sp_replcmds' - MSSQL_REPL20011 and MSSQL_REPL22037 RRS feed

  • Question

  • Hi All,

    i am facing issue with Transnational replication. there is NO errors in Distributor to Subscriber. seems issue in Publisher to Distributor(Not sure). I can see below errors in REPL monitor,

    The process could not execute 'sp_replcmds' on (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
    Get help: http://help/MSSQL_REPL20011

    The statement has been terminated. (Source: MSSQLServer, Error number: 3621)
    Get help: http://help/3621

    The process could not execute 'sp_replcmds' on . (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
    Get help: http://help/MSSQL_REPL22037

    Database owner set to SA on Primary and Secondary. 

    in Addition to that below  is the error in MSRepl_Errors table,

    The process could not execute 'sp_replcmds' on 'PUBLISHER'.
    Query timeout expired

    Please assist on this  issue. 

    Regards,

    SJB,


    • Edited by Syed JB Wednesday, July 31, 2019 7:01 AM
    Wednesday, July 31, 2019 6:58 AM

All replies

  • Hi Syed JB,

     

    >>The process could not execute 'sp_replcmds' on 'PUBLISHER'.

    Query timeout expired

     

    Your log reader agent is timing out due to an unknown reason.the timeout issue could by caused by that the transaction log increases a lot in a period of time.Your tlog has filled up because there are replication commands in the log which have not been picked up.

     

    You can increase the value of QueryTimeout property and decrease the value of ReadBatchSize property of the Log Reader Agent to solve this problem temporarily, you can change these value by using the following steps:

     

    1. Right-click the "Replication" node in Management Studio, and choose Launch Replication Monitor.

    2. Right click the subscription and choose "Agent Profile".

     

    Or query msrepl_commands to see if you can figure out which article is generating all these commands and drop this article from the subscriptions and then add it back again or  think about re-initializing the replication.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, August 1, 2019 6:28 AM
  • thanks for the response. my issue resolved by creating missing indexes in distribution database, statistics update with FULLSCAN on  msrepl_commands and transaction table. also I've changed timeout settings in LogReader agent.
    Thursday, August 22, 2019 7:40 PM