May 25, 2016 at 11:29 am
Is there some way.. that I can have a Job that will check to see when a subscriber last finished processing a snapshot, and if there is any replications waiting to be applied? The reason for this.. is so when my apps do their main data load, from the various data mart's, I want to make sure their data is not stale. (this is done at 1am, so I am not going to be up every morning checking the log viewer.)
May 26, 2016 at 8:46 am
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]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply