Checking Subscriber replication status when running a job

  • 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.)

  • 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