Few days back I had to query to my production sql replication server to find what all publications are running and what is the status for each of those. A search on the web found me this (unfortunately I don’t know the author of this to give credit ) :
\nSELECT
\n(CASE
\nWHEN mdh.runstatus = ‘1’ THEN ‘Start – ‘+cast(mdh.runstatus as varchar)
\nWHEN mdh.runstatus = ‘2’ THEN ‘Succeed – ‘+cast(mdh.runstatus as varchar)
\nWHEN mdh.runstatus = ‘3’ THEN ‘InProgress – ‘+cast(mdh.runstatus as varchar)
\nWHEN mdh.runstatus = ‘4’ THEN ‘Idle – ‘+cast(mdh.runstatus as varchar)
\nWHEN mdh.runstatus = ‘5’ THEN ‘Retry – ‘+cast(mdh.runstatus as varchar)
\nWHEN mdh.runstatus = ‘6’ THEN ‘Fail – ‘+cast(mdh.runstatus as varchar)
\nELSE CAST(mdh.runstatus AS VARCHAR)
\nEND) [Run Status],
\nmda.subscriber_db [Subscriber DB],
\nmda.publication [PUB Name],
\nright(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],
\nCONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized],
\nund.UndelivCmdsInDistDB [UndistCom],
\nmdh.comments [Comments],
\n‘select * from distribution.dbo.msrepl_errors (nolock) where id = ‘ + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
\nmdh.xact_seqno [SEQ_NO],
\n(CASE
\nWHEN mda.subscription_type = ‘0’ THEN ‘Push’
\nWHEN mda.subscription_type = ‘1’ THEN ‘Pull’
\nWHEN mda.subscription_type = ‘2’ THEN ‘Anonymous’
\nELSE CAST(mda.subscription_type AS VARCHAR)
\nEND) [SUB Type],<\/p>\n
mda.publisher_db+’ – ‘+CAST(mda.publisher_database_id as varchar) [Publisher DB],
\nmda.name [Pub – DB – Publication – SUB – AgentID]
\nFROM distribution.dbo.MSdistribution_agents mda
\nLEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
\nJOIN
\n(SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
\nFROM distribution.dbo.MSrepl_commands t (NOLOCK)
\nJOIN distribution.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
\nJOIN
\n(SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq
\nFROM distribution.dbo.MSdistribution_history hist (NOLOCK)
\nJOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
\nFROM distribution.dbo.MSdistribution_history (NOLOCK)
\nGROUP BY agent_id) AS h
\nON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
\nGROUP BY hist.agent_id, h.maxseq
\n) AS MaxAgentValue
\nON MaxAgentValue.agent_id = s.agent_id
\nGROUP BY s.agent_id, MaxAgentValue.[time]
\n) und
\nON mda.id = und.agent_id AND und.[time] = mdh.[time]
\nwhere 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.
\n–and mdh.runstatus=’6′ –Fail
\n–and mdh.runstatus<>’2′ –Succeed
\norder by mdh.[time]<\/p>\n