Sql server replication – get last synchronization date from query

replicationsqlsql-serversynchronization

Does anyone know the query the last synchronization date from sql server (2008).

It is the same information displayed in replication monitor, but I want to be able to get that date from a query.

Best Solution

I created a view like this to get last date by the subscriber

select subscriber_name, max(start_time) as last_sync 
from msMerge_sessions inner join msMerge_agents 
   on msmerge_agents.id = msmerge_sessions.agent_id 
group by subscriber_name

I called the view 'LastSync' - I then joined that view like this to get a representation similar to what the replication monitor shows.

SELECT     dbo.LastSync.id, dbo.LastSync.subscriber_name, dbo.LastSync.creation_date, dbo.LastSync.last_sync, 
                  distribution.dbo.MSmerge_sessions.estimated_upload_changes + distribution.dbo.MSmerge_sessions.estimated_download_changes AS estimate_rows, 
                  distribution.dbo.MSmerge_sessions.upload_inserts + distribution.dbo.MSmerge_sessions.upload_updates + distribution.dbo.MSmerge_sessions.upload_deletes + distribution.dbo.MSmerge_sessions.download_inserts
                   + distribution.dbo.MSmerge_sessions.download_updates + distribution.dbo.MSmerge_sessions.download_deletes AS actual_rows, 
                  distribution.dbo.MSmerge_sessions.duration AS total_seconds, distribution.dbo.MSmerge_sessions.percent_complete, 
                  distribution.dbo.MSmerge_sessions.delivery_rate, CASE (runstatus) 
                  WHEN 1 THEN 'Start' WHEN 2 THEN 'Succeed' WHEN 3 THEN 'In Progress' WHEN 4 THEN 'Idle' WHEN 5 THEN 'Retry' WHEN 6 THEN 'Fail' END AS Status
FROM         distribution.dbo.MSmerge_sessions INNER JOIN
                  dbo.LastSync ON dbo.LastSync.id = distribution.dbo.MSmerge_sessions.agent_id AND distribution.dbo.MSmerge_sessions.start_time = dbo.LastSync.last_sync