question

Many says that there could ba an issue with the database owner, don't know if this is valid in your case...
https://www.sqlservercentral.com/forums/topic/the-process-could-not-execute-sp_replcmds-on-servernamepublisher-generated-by-the-log-reader
https://www.dcac.com/sql/the-process-could-not-execute-sp_replcmds-on#:~:text=Often%20this%20error%20message%20can,stored%20procedure%20as%20shown%20below.

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://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-distribution-agent?view=sql-server-ver15

Hi @aemhD-7221,

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.

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.

Hi @aemhD-7221,

"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.

Thanks, yes it seems working and needed more time, it finally deliver the transactions to each subscriber after more than a day.
Many thanks.

Is there any way to rapid the process for each subscriber ? I have over million undistributed commands for each subscriber now.

Hi @aemhD-7221,

There are some other methods for your reference.
Way 1: CREATE New SQL Stored Procedure
It will help execute the Stored Procedure article on the Subscriber instead of replicating all individual data changes that were happening.
Way 2: Configuring MaxCmdsInTran, ReadBatchSize, and ReadBatchThreshold Log Reader Agent Parameters
Way 3: Configuring the Best Values for SubscriptionStreams Parameter
The default value is 1 suggesting only one stream or connection from the distribution to the subscriber database. Values can be any between 1 to 64. If more Subscription streams are added, it might end up on CXPACKET congestion (in other words, parallelism). Hence, you should take care while configuring this option in Production.

For more details and explanation, please see the link i post before.

Best regards,
Seeya