Getting Replication Snapshot Delivery Times

Getting Replication Snapshot Delivery Times

We use transactional replication quite heavily in our environment. We have multiple publishers, distributors and subscribers. Currently we have three subscribers overseas, one in London and two in Sydney. When we were setting up the second subscriber in Sydney I was asked how long it was going to take. My answer was, "More than an hour but less than two days". Really I had no idea, we had only ever reinitialized a publication or two out to Sydney but never tried to setup all sixty four international subscriptions and initialize them all at once. I started around Midnight on Friday night setting up replication (from scripts of course, you never want to use the GUI for a go live event) and it finished around 10 pm Saturday night. I decided I wanted to know which publications took the longest.

Normally I figure out the times by opening up Replication monitor, expanding out the publication, right clicking on the subscription and selecting "View Details". Then I look for the following lines.

First I look for the start time:

Then I look for the completion event:

I grab the date and times from these two events and using a little math (or excel) and I can figure out how long the snapshots took to deliver. 

So, I opened a new excel sheet, created a row for the first publication and started logging the data. I looked down the page and realized I did not want to do this process sixty three more times. I figured the data in replication monitor had to be stored somewhere on one of the servers involved and there must be a way to query it. I assumed (correctly) that I could get the information from the distributor and after poking around I found the tables I needed to use. Below is the completed query 

USE distribution_Research

GO

DECLARE @mysub sysname,

    @mystart DATETIME

-- Variables - change these you will

SET @mysub = 'SubscriberDB-PRD'

SET @mystart = '2018-02-24 00:00:00.000'

-- Query - touch these you will not

SELECT DISTINCT pub.name,

    ms.publisher_db,

    mp.publication,

    sub.name,

    ms.subscriber_db,

    msh.start_time,

    msh.time AS endtime,

    CAST((msh.time - msh.start_time) AS TIME) AS duration,

    msh.comments

FROM dbo.MSdistribution_history AS msh

JOIN dbo.MSsubscriptions AS ms

    ON ms.agent_id = msh.agent_id

JOIN master.sys.servers AS pub

    ON ms.publisher_id = pub.server_id

JOIN master.sys.servers AS sub

    ON ms.subscriber_id = sub.server_id

JOIN dbo.MSpublications AS mp

    ON ms.publication_id = mp.publication_id

WHERE 1=1

AND sub.name = @mysub

--AND ms.subscriber_db = 'Returns'

AND msh.start_time > @mystart

AND (msh.comments LIKE 'Delivered%')

ORDER BY mp.publication, msh.start_time DESC

Using this I was able to get the the times for all the publications at once and throw them into excel. I combined the data with some row counts and tables sizes and was able to make some pretty nice reports to show my boss. Hopefully this will help someone else do the same.