transaction replication issue

Question

transaction replication issue

Heisenberg 266

We have a transaction replication with 1 publisher and 2 subscriber, its a push subscription. We had an issue couple of days back, where pending commands were growing continuously for one article. After running sp_whoisactive command on the subscriber I have seen there was an update running continuously on both the subscribers. It looks like this update session was running different updates each few seconds ( i found that out from sp_whoisactive and the first column dd:hh:mi:ss was showing last few seconds each time i run this sp). I'm not sure if it was running same update again and again or different updates in each of those batches.

I saw no blocks , and errorlog didnt show any error. We ended up restarting sql services for both the subscribers and after restarting sql server services, withint 30 mins subscribers started to catch up pending updates/commands.

What might have happened in this scenario , we have a monitoring tool SQL Sentry that captures any statement that has been run in the past , is there any way to find it out what might have happened and are there any steps to troubleshoot these kind of issues?

CathyJi-MSFT 22,431 Reputation points Microsoft External Staff

2022-03-08T06:23:14.787+00:00

Hi @SQLServerBro,

Did you get any useful information from replication monitor? Any latency in subscription list? If you have find any latency, please follow below blog to troubleshoot latency issue in replication.

Troubleshooting transactional replication latency issues in SQL Server
Heisenberg 266 Reputation points

2022-03-09T22:45:08.39+00:00

hello @CathyJi-MSFT thank you for the response, link was helpful. however im still not able to figure out in a slow distributor to subscriber process, how can i find out which command or statement is being delivered at the moment particularly if there is large DML batch executed on the publisher.