It looks like I can set up a table to hold some information: has my main chain of jobs started today, has my daily snapshot written its files, is there any replication to subscribers going on. Not sure if this will give me my complete picture, but for the last two questions... the following two queries should answer.

Both of these should end up with a zero in the column "gonogo", that would mean there isn't any replication going on.

To see if a snapshot is generating:

SELECT top 1

runstatus,

into #check

FROM dbo.MSsnapshot_history

order by start_time desc, [time] desc

select case when runstatus = 2 then 0 else 1 end as gonogo

, runstatus

from #check

To see if any replication to the subscriber is going on:

drop table #check2

SELECT

(CASE

WHEN mdh.runstatus = '1' THEN 1

WHEN mdh.runstatus = '2' THEN 1

WHEN mdh.runstatus = '3' THEN 1

WHEN mdh.runstatus = '4' THEN 0

WHEN mdh.runstatus = '5' THEN 1

WHEN mdh.runstatus = '6' THEN 1

ELSE 1

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],

mdh.comments [Comments],

'select * from distribution.dbo.msrepl_errors (nolock) where id = ' + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],

mda.publisher_db+' - '+CAST(mda.publisher_database_id as varchar) [Publisher DB],

mda.name [Pub - DB - Publication - SUB - AgentID]

into #check2

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'

order by mdh.[time]

select sum([Run Status]) as gonogo

from #check2

group by [Run Status]