I am seeing an unusually high delivery latency between our distributor and subscribers and I do not understand why.

We have in this configuration 3 sql servers using transactional push replication to replicate data from one master server to two reporting servers.

We have 9 publications. The distribution agent for most publications are showing under 5ms, but one is show as 2000+ms to both subscribers.

The suspect publication has only 4 small articles (tables) that rarely, if ever, change. I've checked and each table has an primary key.

I've also checked the @status parameter for each article according to the MS KB: The distribution agent may experience high latency when you configure transactional replication with articles that are configured not to replicate changes as parameterized statements.

I'm tempted to start dropping articles to find out if one particular table is the culprit.

Does anyone have any suggestions as to what I can look at?

  • 1
    Did this just start happening recently, or has it been this way all along? And can you confirm there actually is a performance issue with late deliveries on the subscriber side?
    – Jon Seigel
    Mar 15, 2013 at 16:07

1 Answer 1


Below script will help you find out how many undistributed commands are there. Also, do you see any errors in sql server or windows logs ? Is it just slow or any failures reported ?

           WHEN mdh.runstatus = '1' THEN 'Start - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '2' THEN 'Succeed - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '3' THEN 'InProgress - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '4' THEN 'Idle - ' + CAST(mdh.runstatus AS 
           WHEN mdh.runstatus = '5' THEN 'Retry - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '6' THEN 'Fail - ' + CAST(mdh.runstatus AS 
           ELSE CAST(mdh.runstatus AS VARCHAR) 
         END )                                   [Run Status], 
       mda.subscriber_db                         [Subscriber DB], 
       mda.publication                           [PUB Name], 
       RIGHT(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 )), 
       Len(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 ))) - ( 
       10 + Len(mda.publisher_db) + ( CASE 
                                          WHEN mda.publisher_db = 'ALL' THEN 1 
                                          ELSE Len(mda.publication) + 2 
                                        END ) )) [SUBSCRIBER], 
       CONVERT(VARCHAR(25), mdh.[time])          [LastSynchronized], 
       und.undelivcmdsindistdb                   [UndistCom], 
       mdh.comments                              [Comments], 
       'select * from distribution.dbo.msrepl_errors (nolock) where id = ' + 
       CAST(mdh.error_id AS VARCHAR(8))          [Query More Info], 
       mdh.xact_seqno                            [SEQ_NO], 
       ( CASE 
           WHEN mda.subscription_type = '0' THEN 'Push' 
           WHEN mda.subscription_type = '1' THEN 'Pull' 
           WHEN mda.subscription_type = '2' THEN 'Anonymous' 
           ELSE CAST(mda.subscription_type AS VARCHAR) 
         END )                                   [SUB Type], 
       mda.publisher_db + ' - ' + CAST(mda.publisher_database_id AS VARCHAR) 
                                                 [Publisher DB], 
       [Pub - DB - Publication - SUB - AgentID] 
FROM   distribution.dbo.msdistribution_agents mda 
       LEFT JOIN distribution.dbo.msdistribution_history mdh 
         ON mdh.agent_id = mda.id 
       JOIN (SELECT s.agent_id, 
                          WHEN xact_seqno > maxagentvalue.maxseq THEN 1 
                          ELSE 0 
                        END) AS undelivcmdsindistdb 
             FROM   distribution.dbo.msrepl_commands t (nolock) 
                    JOIN distribution.dbo.mssubscriptions AS s (nolock) 
                      ON ( t.article_id = s.article_id 
                           AND t.publisher_database_id = s.publisher_database_id 
                    JOIN (SELECT hist.agent_id, 
                                 MAX(hist.[time]) AS [time], 
                          FROM   distribution.dbo.msdistribution_history hist ( 
                                 JOIN (SELECT agent_id, 
                                              Isnull(MAX(xact_seqno), 0x0) AS 
                                 distribution.dbo.msdistribution_history ( 
                                       GROUP  BY agent_id) AS h 
                                   ON ( hist.agent_id = h.agent_id 
                                        AND h.maxseq = hist.xact_seqno ) 
                          GROUP  BY hist.agent_id, 
                                    h.maxseq) AS maxagentvalue 
                      ON maxagentvalue.agent_id = s.agent_id 
             GROUP  BY s.agent_id, 
                       maxagentvalue.[time]) und 
         ON mda.id = und.agent_id 
            AND und.[time] = mdh.[time] 
  • so i ran the script and the database/publication in question has 0 undistributed commands. Im using spotlight on sql server to monitor performance and it indicates that "the distribution agent '...' has a latency of 2148.9 seconds. could this just be a false alarm? Mar 15, 2013 at 19:18
  • If it has 0 undistributed commands, then there is no data to be replicated. Did you check by launching Replication Monitor to see if you are seeing same latency ? Also this will help you with TSQL Code sqlfool.com/2008/11/checking-replication-latency-with-t-sql
    – Kin Shah
    Mar 15, 2013 at 19:30
  • sql server replication monitor does quite frequently report that the perfomance is critical with latencies of over ... waiting for it to happen again ... 00:02:17 on one or both subscriptions (this occurance was just on one). This database is mainly a reference database and is rarely written to (maybe once a week) so it doesnt surprise me that there are no transactions to be replicated. Why though would there be high latency? I did do a tracer token and from pub to dist the latency is 00:00:04, from dist to sub it is 00:00:03. Mar 18, 2013 at 12:52
  • 1
    What is your replication setting ? Is it running continuously or every 5 mins, etc ? Just another thought ...If this database is rarely written (once a week) why are you using T-Rep ? You do have an option of snapshot replication. Also, the tracer token latency of 4 sec is not that high which needs a concern ... Did you check your N/W settings or anything has changed on your server that would affect this all of a sudden ?
    – Kin Shah
    Mar 18, 2013 at 14:08
  • 1
    Thanks ! Unless there is a business need, there is no need for Replication to run continuously. Your case makes sense to schedule it to run every 5 mins or 15 - 30 mins. Did you check the n/w if packets are being dropped using tracert or ping ... and probably with your n/w team to see if there is any change in the n/w ?
    – Kin Shah
    Mar 19, 2013 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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