Subscribe to this blog
Briefcase
Print
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
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