I was asked today to help on a long running hierarchy replication task. I pointed my customer to the default report whilst I was searching a hand crafted SQL to do the same, with a slightly friendlier look to it.
I found it, so I sent it to them and I share it with the Community now:
select rs._eventTime as 'Event time', replace (src.name, '.15-cloud.fr', '') + ' --> ' + replace (dst.name, '.15-cloud.fr', '') + case src.name when 'vbox-atrs5.15-cloud.fr' then ' (Down)' else ' (Up)' end as 'Details', cast(rs.TotalReplicationCount as varchar) as 'Objects (total)', cast (rs.FailedReplicationCount as varchar) as 'Failed', cast (rs.DataTransferred as varchar) as 'Size in KiB', cast (DATEDIFF(mi, rs.[StartTime], rs.[FinishTime] ) as varchar)AS 'Duration (mins)' from Evt_NS_Hierarchy_Replication_Status rs join vSource src on rs.SourceNS = src.Guid join vSource dst on rs.DestinationNS = dst.Guid left join Evt_NS_Hierarchy_Job_Start js on rs.JobID = js.JobID where rs._eventtime > getdate() -7 union select StartTime, i.name, '', '', '', ''-- j.Scope, j.Mode, j.Priority, '' from Evt_NS_Hierarchy_Job_Start j join Item i on j.CauseID = i.Guid where j.StartTime > GETDATE() - 7 order by [Event time] desc
There are a few of notes regarding this report:
- You should change the bold section to meet your environment. In my case the servers are registered with their FQDN, so I shorten it from the view (it brings no value to me). Also the child server is named vbox-atrs5.
- The view by default reports on the last 7 days replication tasks.
- The view aggregates the tasks that start the replication and the replication status. It's not always the prettiest but it aggregates data available so you can relate the replication data to the replication item that initiated it.