0

I am running into strange situation currently. I have a transactional replication configured between 2 servers. When I update a record in one article it gets replicated after 30 mins. I am unable to figure out why.

Log reader and distributor agent is setup to run continuously. There is no replication latency/errors found in replication monitor. But still it happens.

A tracer token is very fast. It just shows 5 secs as overall latency. The article I am updating is added into multiple publication and my environment is SQL Server 2012. No blocking. SQL Server is running fine. I don't see any resource intensive queries running on the server.

I would appreciate if any one could help me figure out this issue.

closed as off-topic by Michael Green, Philᵀᴹ, Andriy M, James Anderson, billinkc Aug 22 '16 at 11:48

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Michael Green, Philᵀᴹ, Andriy M, James Anderson, billinkc
If this question can be reworded to fit the rules in the help center, please edit the question.

2

If the tracer token goes through in about 5 seconds and your updated data is taking 30 minutes, then the data has not been committed on the publisher yet. The tracer token would not have gone through if there was an un-replicated row ahead of it. Run a trace or an XE session on the publisher to see what is going on there.

1

It turned out that those articles were not subscribed. It was getting synchronised through a job. When I checked in publication properties the article was there. So it was added in publication. But its subscription was not there. I came to know this by querying MSsubscriptions table in distribution database. Thank you all for you comments and answers.

-1

In such scenario (been there done that), I'd suggest that you use tracer token to figure out whether the delay is caused by Log Reader Agent or Distribution Agent. Here is the link https://msdn.microsoft.com/en-us/library/ms151178.aspx

Very generic guideline is:

  1. Check the log size of the publishing database, if it is too big, log reader agent may take long time to process it.
  2. Check whether there is any blocking on the publisher side. When you update a record in an article, does that update really get committed? i.e. the t-log is written to the log file?
  3. Check whether your distributor is busy and esp. whether it has a long list of transaction commands that need to be distributed?

This article is a good example of using tracer token to debug latency issue https://blogs.msdn.microsoft.com/repltalk/2010/03/11/divide-and-conquer-transactional-replication-using-tracer-tokens/

Hope this helps.

Not the answer you're looking for? Browse other questions tagged or ask your own question.