Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Distribution Agent reports the transactional replication latency incorrectly in SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012


View products that this article applies to.

Symptoms

When you query the latency of a transactional replication in Microsoft SQL Server 2008, in SQL Server 2008 R2, or in SQL Server 2012, the Distribution Agent returns an incorrect value. 

For example, you run the following command to query the latency of a transactional replication:
select delivered_commands, current_delivery_latency, delivery_latency, time from distribution..msdistribution_history where agent_id=(select id from distribution..MSdistribution_agents where subscriber_db='<SubscriberDB>') order by time desc
However, you receive the following incorrect result:
delivered_commandscurrent_delivery_latencydelivery_latencyTime
118184318332012-06-20 12:39:30.910
1195020862519592012-06-20 12:38:50.890
1192019461018892012-06-20 12:33:50.750
18018232012-06-20 12:28:50.590
The information in this table incorrectly reports that the latency is increasing. The incorrect latency value is displayed in the following locations:
  • The current_delivery_latency column in the msdistribution_history table in the distribution database
  • The delivery_latency column in the msdistribution_history table in the distribution database
  • The Distribution:Delivery Latency performance counter in Performance Monitor

↑ Back to the top


Cause

This problem occurs because an incorrect calculation intermittently occurs during the synchronization process. Therefore, increased latency is calculated and reported in the msdistribution_history table.

↑ Back to the top


Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

↑ Back to the top


Workaround

When you experience this problem, alerts that you have configured to detect the latency of transactional replications are invalid. For example, you set up an alert that notifies you when the latency of replication is larger than 30,000 milliseconds. However, the alert frequently reports periods of latency when the subscription is actually synchronized.

To work around this problem, follow those steps:
  1. Create a second identical alert that detects a smaller latency value (for example, 20,000 milliseconds).
  2. Configure the second alert to execute a job that inserts a tracer token into the publication. To do this, use the following statement when the condition is true:
    EXEC sys.sp_posttracertoken @publication = '<SubscriberDB>'
When the second alert is triggered by an incorrect latency value, the execution of this procedure will clear the problem. If the latency value is correct, the latency will continue to increase past 30,000 milliseconds. Therefore, the first alert is triggered correctly, and you receive notification of the correct latency value.

↑ Back to the top


Keywords: kbtshoot, kbsurveynew, kbexpertiseadvanced, kbprb, kb

↑ Back to the top

Article Info
Article ID : 2727217
Revision : 1
Created on : 1/7/2017
Published on : 11/19/2012
Exists online : False
Views : 366