none
Monitor Replication Latency RRS feed

  • Question

  • Hi, We have setup the replication alerts to notify us if the latency goes beyond 1 hour and we use the below command. But the counter value goes very high despite there is no actual latency and we get flooded with latency emails. The value resets to normal as soon as we restart the distribution agents. Is this expected behavior? it happens both on SQL 2016\2014 distributors.Thank you!

    SELECT * FROM msdb.sys.dm_os_performance_counters WITH(NOLOCK) WHERE counter_name ='Dist:Delivery Latency'

    AND cntr_value > 60000*60



    Saturday, April 22, 2017 12:56 PM

All replies

  • I've noticed this myself. I am not sure what the answer is.

    I use tracer tokens and monitor their history to get a good idea of latency.

    Saturday, April 22, 2017 4:05 PM
    Moderator
  • Thanks Hilary! May be i ll report this behavior at Microsoft connect.
    Sunday, April 23, 2017 12:26 AM
  • Hi SQLmaddy,

    If I understand this correctly, it might just because you have large transaction(s) that needs to be delivered to subscribers as described in this blog.

    I’ve also found a KB article that indicates that it’s a known issue in SQL Server 2008/2008R2/2012, but I’m not sure whether it’s apply to SQL Server 2014/2016 or not. I’ll see what I can do to clarify this and keep you posted in this thread. 

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 25, 2017 2:57 AM
    Moderator
  • You may try to use a bit different approach of getting this info.

    I'm using a bit different way of getting delivery Latency.  Here is the Im using for each distribution db. It will give you latency per agent (as we have multiple subscriptions)

    with history_CTE (agent_id,time)
    As
    (
    select agent_id,max(time) as time from msdistribution_history    with(nolock)    
         where runstatus in (2,3,4,5,6,7)      
       group by agent_id
    )


    select a.name,delivery_latency/1000  as 'Latency in Sec' from msdistribution_history h
    join msdistribution_agents a
    on h.agent_id = a.id
    join history_CTE cte
    on h.agent_id=cte.agent_id
    and h.time = cte.time

    Thursday, May 18, 2017 8:48 AM