I want to be able to check the status of a publication and subscription in SQL Server 2008 T-SQL. I want to be able to determine if its okay, when was the last successful, sync, etc.. Is this possible?
How do I check SQL replication status via T-SQL?
Asked Answered
I know this is a little late....
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]
That's a fantastic query there! –
Cussed
that's a great script indeed the only change I have done is to get the subscriber server. I have replaced this line:
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], by this line: 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], –
Shantell Old post but adding for others - The following stored procedure will give you what you need:
- sp_replmonitorhelppublication (https://msdn.microsoft.com/en-us/library/ms186304.aspx)
It allows you to view the status plus other information for all replication publications for a distributor (run it on the distribution database)
In case it's helpful, I've combined parts of the two answers given and taken sp_replmonitorhelppublication and sp_replmonitorhelpsubscription and placed them into Temp Tables so I can sort them and exclude columns as needed.
Note, the subscriber script excludes some merge columns from sp_replmonitorhelpsubscription as I'm not looking for merge replication data.
-------------------------------------------------------------------------------------------------------------------------
-- 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;
© 2022 - 2024 — McMap. All rights reserved.