Replication issue

aemhD 1

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.

7 answers

aemhD 1 Reputation point

2021-11-18T11:05:47.467+00:00

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.
Please sign in to rate this answer.
Tom Phillips 17,771 Reputation points

2021-11-18T13:19:10.07+00:00

When this problem happens, it is usually faster to reinit the subscribers than wait for the queue to flush all the commands from the queue. Unless your database is very big, or the connection to the subscribers is very slow.

Tom Phillips 17,771 Reputation points

2021-11-18T13:20:33.977+00:00

If this happens often, you should change CommitBatchSize to a more reasonable value like 100. This will generally avoid the problem.

aemhD 1 Reputation point

2021-11-18T19:30:11.52+00:00

ok, I have reverted the CommitBatchSize to the default value to 100, hoping to affect the process.
I am really try to avoid reinitialize process, it would be a such headache for such database and subscribers count.

Thanks again for your both kind help
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Seeya Xi-MSFT 16,586 Reputation points

2021-11-19T02:08:28.437+00:00

Hi @aemhD ,

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
Please sign in to rate this answer.
aemhD 1 Reputation point

2021-11-19T11:18:00.547+00:00

Thanks.. I have already adjusted the mentioned parameters, but the transaction seems to be highly increasing and nothing is replicated although!

I unfortunately , very sad to say, will have to reintit

So, in general, what to advice for better performance, and avoid such issue that I even can't find what caused it!

Tom Phillips 17,771 Reputation points

2021-11-19T13:12:40.973+00:00

You apparently have a lot of individual transactions to apply to the subscribers. Setting CommitBatchSize to a value which works is the simplest answer. I would try something like 5 and let it finish and run a few days. Then ramp it up to 10-25 and see how it goes.

Also, increate QueryTimeOut to a huge number like 50000.

Tom Phillips 17,771 Reputation points

2021-11-19T13:14:42.547+00:00

Also, I highly suggest you do NOT run the distributor in "Continuous", but schedule it to run every few mins. In Continuous mode, it never closes the log and you cannot see the messages easily.

Seeya Xi-MSFT 16,586 Reputation points

2021-11-23T01:33:50.283+00:00

Hi @aemhD ,

Let's sort it out. > This error occurs most often when you have a large transaction(s).
You can normalize your big transaction so that it is not a single execution, such as creating a stored procedure. It will help execute the Stored Procedure article on the Subscriber instead of replicating all individual data changes that were happening.
Or set the maximum value of some parameters to control the transaction volume.
How to create a stored procedure and set the maximum value is very detailed in the link I provided before. You can read that article, I believe this is helpful to you.
Have a nice day.

Best regards,
Seeya

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

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Replication issue

7 answers

Your answer