Clever Geek Handbook

Sql server replication - get latest sync date from query

Does anyone know a query about the latest sync date from SQL Server (2008).

This is the same information shown in the replication monitor, but I want to get this date from the request.

+1


source to share


3 answers


You can see a lot of information about merge sessions using the msMerge_sessions system table:

select * from msMerge_sessions


Use other system tables available in your database depending on the information you require.

+2


source


I created a view like this to get the latest date by 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 named the view "LastSync". Then I joined this view to get a view 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
+3


source


To answer number 3

A lot of effort, but there are some modifications to On view to be able to run Query

---- Create LastSync View as below

Create View LastSync As 
select subscriber_name, max(start_time) as last_sync, ID, creation_date
from msMerge_sessions inner join msMerge_agents 
   on msmerge_agents.id = msmerge_sessions.agent_id 
group by subscriber_name, ID, creation_date
Go

---- Run below request

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 ASDownload_changes + 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, distributiondumerges.download_deletes AS actual_rows, distribution.download. AS total_seconds, distribution.dbo.MSmerge_sessions.percent_complete, distribution.dbo.MSmerge_sessions.delivery_rate, CASE (runstatus) WHEN 1 THEN "FIND" WHEN 2 THEN "WILL OVERCOME" WHEN 3 THEN "IN PROMOTION"WHEN 4 THEN "BAD" WHEN 5 THEN "Repeat" WHEN 6 THEN "Failed" 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

- Luck

0


source






More articles:


All Articles