我希望能够在SQL Server 2008 T-SQL中检查发布和订阅状态。我想确定它是否正常，上次成功同步是什么时候等等..这可能吗？
3个回答
39
我知道有点晚了，但是……
SELECT
(CASE
WHEN mdh.runstatus = '1' THEN 'Start - '+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = '2' THEN 'Succeed - '+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = '3' THEN 'InProgress - '+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = '4' THEN 'Idle - '+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = '5' THEN 'Retry - '+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = '6' THEN 'Fail - '+cast(mdh.runstatus as varchar)
ELSE CAST(mdh.runstatus AS VARCHAR)
END) [Run Status],
mda.subscriber_db [Subscriber DB],
mda.publication [PUB Name],
right(left(mda.name,LEN(mda.name)-(len(mda.id)+1)), LEN(left(mda.name,LEN(mda.name)-(len(mda.id)+1)))-(10+len(mda.publisher_db)+(case when mda.publisher_db='ALL' then 1 else LEN(mda.publication)+2 end))) [SUBSCRIBER],
CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized],
und.UndelivCmdsInDistDB [UndistCom],
mdh.comments [Comments],
'select * from distribution.dbo.msrepl_errors (nolock) where id = ' + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
mdh.xact_seqno [SEQ_NO],
(CASE
WHEN mda.subscription_type = '0' THEN 'Push'
WHEN mda.subscription_type = '1' THEN 'Pull'
WHEN mda.subscription_type = '2' THEN 'Anonymous'
ELSE CAST(mda.subscription_type AS VARCHAR)
END) [SUB Type],
mda.publisher_db+' - '+CAST(mda.publisher_database_id as varchar) [Publisher DB],
mda.name [Pub - DB - Publication - SUB - AgentID]
FROM distribution.dbo.MSdistribution_agents mda
LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
JOIN
(SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
FROM distribution.dbo.MSrepl_commands t (NOLOCK)
JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
JOIN
(SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq
FROM distribution.dbo.MSdistribution_history hist (NOLOCK)
JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
FROM distribution.dbo.MSdistribution_history (NOLOCK)
GROUP BY agent_id) AS h
ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
GROUP BY hist.agent_id, h.maxseq
) AS MaxAgentValue
ON MaxAgentValue.agent_id = s.agent_id
GROUP BY s.agent_id, MaxAgentValue.[time]
) und
ON mda.id = und.agent_id AND und.[time] = mdh.[time]
where mda.subscriber_db<>'virtual' -- created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change.
--and mdh.runstatus='6' --Fail
--and mdh.runstatus<>'2' --Succeed
order by mdh.[time]
- Denaem
2
9
虽然这篇文章有些老，但为了其他人也添加一下——以下存储过程可以给你所需的信息：
- sp_replmonitorhelppublication (https://msdn.microsoft.com/en-us/library/ms186304.aspx)
它允许您查看分发器上所有复制出版物的状态及其他信息（在分发数据库上运行）。
- Jamie Pollard
1
如果有帮助的话，我已经将两个答案中的部分内容合并，并使用临时表sp_replmonitorhelppublication和sp_replmonitorhelpsubscription进行排序和排除不需要的列。请注意，订阅者脚本从sp_replmonitorhelpsubscription中排除了一些合并列，因为我不需要查找合并复制数据。
-------------------------------------------------------------------------------------------------------------------------
-- PUBLISHER SCRIPT
-------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID ('tempdb..#tmp_replicationPub_monitordata') IS NOT NULL DROP TABLE #tmp_replicationPub_monitordata;
CREATE TABLE #tmp_replicationPub_monitordata (
publisher_db sysname
, publication sysname
, publication_id INT
, publication_type INT
, status INT -- publication status defined as max(status) among all agents
, warning INT -- publication warning defined as max(isnull(warning,0)) among all agents
, worst_latency INT
, best_latency INT
, average_latency INT
, last_distsync DATETIME -- last sync time
, retention INT -- retention period
, latencythreshold INT
, expirationthreshold INT
, agentnotrunningthreshold INT
, subscriptioncount INT -- # of subscription
, runningdistagentcount INT -- # of running agents
, snapshot_agentname sysname NULL
, logreader_agentname sysname NULL
, qreader_agentname sysname NULL
, worst_runspeedPerf INT
, best_runspeedPerf INT
, average_runspeedPerf INT
, retention_period_unit TINYINT
, publisher sysname NULL
);
INSERT INTO #tmp_replicationPub_monitordata
EXEC sp_replmonitorhelppublication;
SELECT (CASE WHEN status = '1' THEN 'Start - ' + CAST(status AS VARCHAR)
WHEN status = '2' THEN 'Succeed - ' + CAST(status AS VARCHAR)
WHEN status = '3' THEN 'InProgress - ' + CAST(status AS VARCHAR)
WHEN status = '4' THEN 'Idle - ' + CAST(status AS VARCHAR)
WHEN status = '5' THEN 'Retry - ' + CAST(status AS VARCHAR)
WHEN status = '6' THEN 'Fail - ' + CAST(status AS VARCHAR)ELSE CAST(status AS VARCHAR)END) [Run Status]
, publisher_db
, publication
, publication_id
, (CASE WHEN publication_type = '0' THEN 'Transactional - ' + CAST(publication_type AS VARCHAR)
WHEN publication_type = '1' THEN 'Snapshot - ' + CAST(publication_type AS VARCHAR)
WHEN publication_type = '2' THEN 'Merge - ' + CAST(publication_type AS VARCHAR)ELSE '' END) AS [Publication Type]
, (CASE WHEN warning = '1' THEN 'Expiration' + CAST(warning AS VARCHAR)
WHEN warning = '2' THEN 'Latency' + CAST(warning AS VARCHAR)
WHEN warning = '4' THEN 'Mergeexpiration' + CAST(warning AS VARCHAR)
WHEN warning = '16' THEN 'Mergeslowrunduration' + CAST(warning AS VARCHAR)
WHEN warning = '32' THEN 'Mergefastrunspeed' + CAST(warning AS VARCHAR)
WHEN warning = '64' THEN 'Mergeslowrunspeed' + CAST(warning AS VARCHAR)END) warning
, worst_latency
, best_latency
, average_latency
, last_distsync
, retention
, latencythreshold
, expirationthreshold
, agentnotrunningthreshold
, subscriptioncount
, runningdistagentcount
, snapshot_agentname
, logreader_agentname
, qreader_agentname
, worst_runspeedPerf
, best_runspeedPerf
, average_runspeedPerf
, retention_period_unit
, publisher
FROM #tmp_replicationPub_monitordata
ORDER BY publication;
-------------------------------------------------------------------------------------------------------------------------
-- SUBSCRIBER SCRIPT
-------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID ('tempdb..#tmp_rep_monitordata ') IS NOT NULL DROP TABLE #tmp_rep_monitordata;
CREATE TABLE #tmp_rep_monitordata (
status INT NULL
, warning INT NULL
, subscriber sysname NULL
, subscriber_db sysname NULL
, publisher_db sysname NULL
, publication sysname NULL
, publication_type INT NULL
, subtype INT NULL
, latency INT NULL
, latencythreshold INT NULL
, agentnotrunning INT NULL
, agentnotrunningthreshold INT NULL
, timetoexpiration INT NULL
, expirationthreshold INT NULL
, last_distsync DATETIME NULL
, distribution_agentname sysname NULL
, mergeagentname sysname NULL
, mergesubscriptionfriendlyname sysname NULL
, mergeagentlocation sysname NULL
, mergeconnectiontype INT NULL
, mergePerformance INT NULL
, mergerunspeed FLOAT NULL
, mergerunduration INT NULL
, monitorranking INT NULL
, distributionagentjobid BINARY(16) NULL
, mergeagentjobid BINARY(16) NULL
, distributionagentid INT NULL
, distributionagentprofileid INT NULL
, mergeagentid INT NULL
, mergeagentprofileid INT NULL
, logreaderagentname sysname NULL
, publisher sysname NULL
);
INSERT INTO #tmp_rep_monitordata
EXEC sp_replmonitorhelpsubscription @publication_type = 0;
INSERT INTO #tmp_rep_monitordata
EXEC sp_replmonitorhelpsubscription @publication_type = 1;
SELECT (CASE WHEN status = '1' THEN 'Start - ' + CAST(status AS VARCHAR)
WHEN status = '2' THEN 'Succeed - ' + CAST(status AS VARCHAR)
WHEN status = '3' THEN 'InProgress - ' + CAST(status AS VARCHAR)
WHEN status = '4' THEN 'Idle - ' + CAST(status AS VARCHAR)
WHEN status = '5' THEN 'Retry - ' + CAST(status AS VARCHAR)
WHEN status = '6' THEN 'Fail - ' + CAST(status AS VARCHAR)ELSE CAST(status AS VARCHAR)END) [Run Status]
, publisher_db
, publication
, (CASE WHEN warning = '1' THEN 'Expiration' + CAST(warning AS VARCHAR)
WHEN warning = '2' THEN 'Latency' + CAST(warning AS VARCHAR)
WHEN warning = '4' THEN 'Mergeexpiration' + CAST(warning AS VARCHAR)
WHEN warning = '16' THEN 'Mergeslowrunduration' + CAST(warning AS VARCHAR)
WHEN warning = '32' THEN 'Mergefastrunspeed' + CAST(warning AS VARCHAR)
WHEN warning = '64' THEN 'Mergeslowrunspeed' + CAST(warning AS VARCHAR)END) warning
, subscriber
, subscriber_db
, (CASE WHEN publication_type = '0' THEN 'Transactional - ' + CAST(publication_type AS VARCHAR)
WHEN publication_type = '1' THEN 'Snapshot - ' + CAST(publication_type AS VARCHAR)
WHEN publication_type = '2' THEN 'Merge - ' + CAST(publication_type AS VARCHAR)ELSE '' END) AS [Publication Type]
, (CASE WHEN subtype = '0' THEN 'Push - ' + CAST(subtype AS VARCHAR)
WHEN subtype = '1' THEN 'Pull - ' + CAST(subtype AS VARCHAR)
WHEN subtype = '2' THEN 'Anonymous - ' + CAST(subtype AS VARCHAR)ELSE '' END) AS SubscriptionType
, latency
, latencythreshold
, agentnotrunning
, agentnotrunningthreshold
, last_distsync
, timetoexpiration
, expirationthreshold
, distribution_agentname
, monitorranking
, distributionagentjobid
, mergeagentjobid
, distributionagentid
, distributionagentprofileid
, mergeagentid
, mergeagentprofileid
, logreaderagentname
, publisher
FROM #tmp_rep_monitordata
ORDER BY publication ASC;
- JM1
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
right(left(mda.name,LEN(mda.name)-(len(mda.id)+1)), LEN(left(mda.name,LEN(mda.name)-(len(mda.id)+1)))-(10+len(mda.publisher_db)+(case when mda.publisher_db='ALL' then 1 else LEN(mda.publication)+2 end))) [SUBSCRIBER],用了这行代码：REVERSE(SUBSTRING(REVERSE(mda.name),CHARINDEX('-',REVERSE(mda.name))+1,CHARINDEX('-',REVERSE(mda.name),CHARINDEX('-',REVERSE(mda.name))+1)-CHARINDEX('-',REVERSE(mda.name))-1)) as [sub],- Marcello Miorelli