sql server 复制 - 从查询中获取上次同步日期

Question

有谁知道从 sql server (2008) 查询上次同步日期。

它与复制监视器中显示的信息相同，但我希望能够从查询中获取该日期。

Answer 1

我创建了一个像这样的视图来获取订阅者的最后日期，

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

我将视图称为“LastSync” - 然后我像这样加入了该视图以获得与复制监视器显示的内容类似的表示。

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

Answer 2

您可以使用系统表 msMerge_sessions 查看有关合并会话的大量信息：

select * from msMerge_sessions

根据您需要的信息，使用数据库中可用的其他系统表。

Answer 3

对于回答号 3，

付出了很大的努力，但在视图上进行了一些修改，以便能够运行查询

---- 创建视图 LastSync 如下

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

---- 运行下面的查询

选择 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 作为实际_行，
distribution.dbo.MSmerge_sessions.duration AS 总秒数，
distribution.dbo.MSmerge_sessions.percent_complete,
distribution.dbo.MSmerge_sessions.delivery_rate，案例（运行状态）
WHEN 1 THEN '开始' WHEN 2 THEN '成功' WHEN 3 THEN '进行中'
WHEN 4 THEN '空闲' WHEN 5 THEN '重试' WHEN 6 THEN '失败' END AS
状态 FROM distribution.dbo.MSmerge_sessions INNER JOIN dbo.LastSync
ON dbo.LastSync.id = distribution.dbo.MSmerge_sessions.agent_id 并且
distribution.dbo.MSmerge_sessions.start_time = dbo.LastSync.last_sync

-- 祝你好运