Is whether a replication is continuous queryable anywhere?

Question

Can I find whether a replication is continuous somewhere in the distribution or msdb databases on SQL Server database servers? (both 2005 and 2008)

Background: I'm making a replication monitor system that will alert (by email) when a replication hasn't succesfully merged within X time. To do this, I'm periodically querying the distribution database's MSreplication_monitordata and MSmerge_sessions tables. To test whether a replication has exceeded its alert time, I do the following for each entry in MSreplication_monitordata to see when the last succesful replication was:

select top 1 end_time
from MSmerge_sessions
where runstatus=2 and agent_id=@AgentId
order by end_time desc

runstatus=2 filters for "complete" status only (see documentation)

The problem is that continuous replications only have a single MSmerge_sessions entry that is continually having its end_time updated, but also has its state as 3, which means "in progress". I can't test for only that, since that would return non-continuous replication sessions which might fail before completing.

I tried seeing if the MSmerge_sessions.current_phase_id column might be useful, but it's undocumented and I can't infer what it means. I've also tried the msdb database, since that's where the replication job records are being kept (in the sysjobs table). The sysjobschedules table seems promising, with some next_run_date and next_run_time fields being 0, but I can't find out how to link that to sysjobs.

score 1 · Accepted Answer · answered Nov 26 '12 at 14:22

Alright, so this is less pretty than I hoped for, but here it is:

select 
    agent.*,
    case when exists(
        select *
        from msdb.dbo.sysjobsteps step
        where agent.job_id=step.job_id
        and step.step_id=2
        and step.command like '%-Continuous%')
        then 1
        else 0
    end as Continuous
from distribution.dbo.MSmerge_agents agent

Deduced from this script, which I found through desperate internet searches. This assumes case-insensitivity for string matches.

If anyone has a better/neater/prettier solution, I'll still award the answer to them.

Is whether a replication is continuous queryable anywhere?

1 Answers1