Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Measuring Transactional Replication Latency Without Tracer Tokens

Measuring Transactional Replication Latency Without Tracer Tokens
 
SQL Server 2005 introduced Tracer Tokens (SQL 2005 | SQL 2008), a new methodology for programmatically measuring replication latency in transactional replication. To measure latency with a tracer token, you simply insert a tracer token at the publisher. The replication process will trace the token as it moves through the steps of the process and report back how long it took for the token to reach the distributor and the subscriber.
 
Sounds great, right? Do you see the problem I see?
 
What's wrong with tracer tokens?
 
There is an inherent problem with tracer tokens. In order to get the results back, replication has to be working well, or you have to wait a long time. For example, if the replication subscriber is 4 hours behind guess how long it will take for the tracer token to get from the distributor to the subscriber.
 
You guessed it: 4 hours!! If replication is latent, and you need to determine by how much, can you really afford to wait for 4 hours to find out? So tracer tokens aren't really very effective when you need them most.
 
It's All in the Distributor
 
There is no great alternative for tracking latency from Publisher to Distributor because you'd have to be able to read the transaction log to determine if there are transactions that have not been read by the log reader. You can calculate replication latency from Distributor to all Subscribers by querying the tracking tables in the distribution database.
 
The simplest way to check latency is simply to let the replication monitor procedures do the work for you and then pull the data from the replication monitor tables. To be sure that we're getting current data, we must first refresh the monitor data by executing sp_replmonitorrefreshjob.
 
The Query
 
Just modify the query below to set your publisher server and publisher database and execute the query in the distribution database.
 

Declare @Publisher sysname,

      @PublisherDB sysname;

 

-- Set Publisher server name

Set @Publisher = 'Publisher server name';

-- Set Publisher database name

Set @PublisherDB = 'Publisher database name';

 

-- Refresh replication monitor data

Exec sys.sp_replmonitorrefreshjob @iterations = 1;

 

With MaxXact (ServerName, PublisherDBID, XactSeqNo)

As (Select S.name, DA.publisher_database_id, max(H.xact_seqno)

      From dbo.MSdistribution_history H with(nolock)

      Inner Join dbo.MSdistribution_agents DA with(nolock)

            On DA.id = H.agent_id

      Inner Join master.sys.servers S with(nolock)

            On S.server_id = DA.subscriber_id

      Where DA.publisher_db = @PublisherDB

      Group By S.name, DA.publisher_database_id)

, OldestXact (ServerName, OldestEntryTime)

As (Select MX.ServerName, Min(entry_time)

      From dbo.msrepl_transactions T with(nolock)

      Inner Join MaxXact MX

            On MX.XactSeqNo < T.xact_seqno

            And MX.PublisherDBID = T.publisher_database_id

      Group By MX.ServerName)

Select [Replication Status] = Case MD.status

                  When 1 Then 'Started'

                  When 2 Then 'Succeeded'

                  When 3 Then 'In progress'

                  When 4 Then 'Idle'

                  When 5 Then 'Retrying'

                  When 6 Then 'Failed'

            End,

      Subscriber = SubString(MD.agent_name,

                              Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 4,

                              Charindex('-', MD.agent_name,

                                    Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 5) -

                                          (Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 4)),

      [Subscriber DB] = A.subscriber_db,

      [Publisher DB] = MD.publisher_db,

      Publisher = MD.publisher,

      [Current Latency (sec)] = MD.cur_latency,

      [Current Latency (hh:mm:ss)] = Right('00' + Cast(MD.cur_latency/3600 As varchar), 2) +

                                                ':' + Right('00' + Cast((MD.cur_latency%3600)/60 As varchar), 2) +

                                                ':' + Right('00' + Cast(MD.cur_latency%60 As varchar), 2),

      [Latency Threshold (min)] = Cast(T.value As Int),

      [Agent Last Stopped (sec)] = DateDiff(hour, agentstoptime, getdate()) - 1,

      [Agent Last Sync] = MD.last_distsync,

      [Last Entry TimeStamp] = OX.OldestEntryTime

From dbo.MSreplication_monitordata MD with(nolock)

Inner Join dbo.MSdistribution_agents A with(nolock)

      On A.id = MD.agent_id

Inner Join dbo.MSpublicationthresholds T with(nolock)

      On T.publication_id = MD.publication_id

      And T.metric_id = 2 -- Latency

Inner Join OldestXact OX

      On OX.ServerName = SubString(MD.agent_name,

                              Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 4,

                              Charindex('-', MD.agent_name,

                                    Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 5) -

                                          (Len(MD.publisher) + Len(MD.publisher_db) + Len(MD.publication) + 4))

Where MD.publisher = @Publisher

And MD.publisher_db = @PublisherDB

And MD.publication_type = 0 -- 0 = Transactional publication

And MD.agent_type = 3; -- 3 = distribution agent

 

Comments

Posted by Anonymous on 25 March 2010

This post was mentioned on Twitter by SQLSoldier: Blogged: Measuring Transactional Replication Latency Without Tracer Tokens: http://bit.ly/aqTyAU #sql #sqlserver #sqlgeek

Posted by Anonymous on 26 March 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Measuring Transactional Replication Latency Without Tracer Tokens - SQL Server Soldier :: News from the frontlines of the database wars         [sqlservercentral.com]        on Topsy.com

Posted by ananth_cs on 12 September 2010

Thanks for the script, it is really useful

Posted by hidayath_be on 30 June 2011

Hi I tried this query.. but i didn't get any output.

I ran this in my distribution Db with passing the publisher and publisherDb details. The query complted successfully but no records has been Display.

Posted by DBASkippack on 26 August 2011

I received the following error message when executing the above script - on SQL 2008 R2 sp1. Thoughts?

Msg 537, Level 16, State 3, Line 9

Invalid length parameter passed to the LEFT or SUBSTRING function.

Posted by DBASkippack on 26 August 2011

Turns out, I used a named instance as the @Publisher.  When I enclosed the named instance in [brackets], the query ran fine but returned NO results...

Posted by UnicornsRreal on 11 January 2012

is this suppose to give any results?

Posted by sqlcentral2 on 1 March 2012

Yeah - if you've reached these comments, abandon all hope.  The query returns empty results.

Posted by Vijendran on 5 March 2013

Thank you, it works well

Posted by shaneat on 11 April 2013

no results

Posted by OmegaZero on 1 May 2013

It returns no results because a) the OldestXact CTE needs a <= instead of a < for XactSeqNo and b) because the string parsing is wrong for the join to oldestXact later on.  Try the below where I fixed these two issues.  Note that due to the way the string is parsed it will ONLY work if you leave the name of the publication agent as the default generated by SQL.  If it still returns no results for you, you probably have something funky in your MD.agent_name so if you want to mess around some with the string parsing you can probably get it working.

---------------------------------------------------------------------------------------

Declare @Publisher sysname,

     @PublisherDB sysname;

-- Set Publisher server name

Set @Publisher = 'Publisher server name';

-- Set Publisher database name

Set @PublisherDB = 'Publisher database name';

-- Refresh replication monitor data

Exec sys.sp_replmonitorrefreshjob @iterations = 1;

With MaxXact (ServerName, PublisherDBID, XactSeqNo)

As (Select S.name, DA.publisher_database_id, max(H.xact_seqno)

     From dbo.MSdistribution_history H with(nolock)

     Inner Join dbo.MSdistribution_agents DA with(nolock)

           On DA.id = H.agent_id

     Inner Join master.sys.servers S with(nolock)

           On S.server_id = DA.subscriber_id

     Where DA.publisher_db = @PublisherDB

     Group By S.name, DA.publisher_database_id)

, OldestXact (ServerName, OldestEntryTime)

As (Select MX.ServerName, Min(entry_time)

     From dbo.msrepl_transactions T with(nolock)

     Inner Join MaxXact MX

           On MX.XactSeqNo <= T.xact_seqno

           And MX.PublisherDBID = T.publisher_database_id

     Group By MX.ServerName)      

Select [Replication Status] = Case MD.status

                 When 1 Then 'Started'

                 When 2 Then 'Succeeded'

                 When 3 Then 'In progress'

                 When 4 Then 'Idle'

                 When 5 Then 'Retrying'

                 When 6 Then 'Failed'

           End,

     Subscriber = replace(replace(replace(left(MD.agent_name,len(MD.agent_name) - charindex('-',reverse(MD.agent_name))),MD.publisher + '-',''),MD.publisher_db + '-',''),left(MD.publication,21) + '-',''),

     [Subscriber DB] = A.subscriber_db,

     [Publisher DB] = MD.publisher_db,

     Publisher = MD.publisher,

     [Current Latency (sec)] = MD.cur_latency,

     [Current Latency (hh:mm:ss)] = Right('00' + Cast(MD.cur_latency/3600 As varchar), 2) +

                                               ':' + Right('00' + Cast((MD.cur_latency%3600)/60 As varchar), 2) +

                                               ':' + Right('00' + Cast(MD.cur_latency%60 As varchar), 2),

     [Latency Threshold (min)] = Cast(T.value As Int),

     [Agent Last Stopped (sec)] = DateDiff(hour, agentstoptime, getdate()) - 1,

     [Agent Last Sync] = MD.last_distsync,

     [Last Entry TimeStamp] = OX.OldestEntryTime

From dbo.MSreplication_monitordata MD with(nolock)

Inner Join dbo.MSdistribution_agents A with(nolock)

     On A.id = MD.agent_id

Inner Join dbo.MSpublicationthresholds T with(nolock)

     On T.publication_id = MD.publication_id

     And T.metric_id = 2 -- Latency

Inner Join OldestXact OX

     On OX.ServerName = replace(replace(replace(left(MD.agent_name,len(MD.agent_name) - charindex('-',reverse(MD.agent_name))),MD.publisher + '-',''),MD.publisher_db + '-',''),left(MD.publication,21) + '-','')                                        

Where MD.publisher = @Publisher AND

MD.publisher_db = @PublisherDB AND

MD.publication_type = 0 -- 0 = Transactional publication

And MD.agent_type = 3; -- 3 = distribution agent

Posted by pamar66 on 27 November 2013

Excellent utility, love it, I had written one myself, but again had to wait until last token was received, or put a threshold for return time.

Posted by subbu.devalla-552721 on 19 February 2014

How can we stored in temp table. I'm getting error

An INSERT EXEC statement cannot be nested.

Is there any way where we can BCP out and the inserted in temp by using BCP

Leave a Comment

Please register or log in to leave a comment.