question

Heisenberg avatar image
0 Votes"
Heisenberg asked Cathyji-msft answered

transaction replication.

what steps do you guys usually take to troublshoot transaction replication.

I occasionally get "performance critical" status next to few publications in the replication monitor. i need to know things you can check particular to replication other than checking SQL server health like blocks/cpu usage etc.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SQLServerBro,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @SQLServerBro,

I occasionally get "performance critical" status next to few publications in the replication monitor.

This means that this transaction replication has latency issues. Please check that if there are latency issues by using Replication Monitor as well as Transact-SQL commands. Check delay from publisher to distributor to subscriber by using tracer tokens. Check the number of virtual log files, they can also be the cause of delay, ect.

Transactional replication latency issues could be caused by many factors, such as network traffic or bandwidth constraints, transactional load on the publisher, system resources, blocking/locking issues and so on. Please read below blogs to troubleshooting transactional replication latency issues.

Troubleshooting transactional replication latency issues in SQL Server
Measure Latency and Validate Connections for Transactional Replication

what steps do you guys usually take to troublshoot transaction replication.

We are take follow steps to troubleshoot error message in transaction replication.

1.Use Replication Monitor to identify at which point replication is encountering the error (which agent?):
•If errors are occurring in the Publisher to Distributor section, the issue is with the Log Reader Agent.
•If errors are occurring in the Distributor to Subscriber section, the issue is with the Distribution Agent.
2.Look through that agent's job history in Job Activity Monitor to identify details of the error. If the job history is not showing enough details, you can enable verbose logging on that specific agent.
3.Try to determine a solution for the error.

Refer to MS document Troubleshooter: Find errors with SQL Server transactional replication to get detail information.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered

Cathy , thank you for the information.

wrt your following statement, How can i find out , issue is in which agent ?

We are take follow steps to troubleshoot error message in transaction replication.
1.Use Replication Monitor to identify at which point replication is encountering the error (which agent?):
•If errors are occurring in the Publisher to Distributor section, the issue is with the Log Reader Agent.
•If errors are occurring in the Distributor to Subscriber section, the issue is with the Distribution Agent.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @SQLServerBro,

wrt your following statement, How can i find out , issue is in which agent ?

Connect to the publisher in SQL Server Management Studio. Expand the server node, right-click the Replication folder, and then select Launch Replication Monitor.

In Replication Monitor, select the replication name, and select the All Subscriptions tab. Right-click the subscription and select View Details.

107926-screenshot-2021-06-22-104540.jpg

107906-screenshot-2021-06-22-104621.jpg

Suggest you read the MS document Troubleshooter: Find errors with SQL Server transactional replication that I mentioned above to get detail information.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.