Replication Monitoring Outside of Enterprise Manager

Question

Replication Monitoring Outside of Enterprise Manager

Terry Bailey

SSC Enthusiast

Points: 126
More actions
January 3, 2006 at 10:00 am

#96247

I wonder if anyone can help me out here.
In enterprise manager the replication monitor indicates if there is a problem with replication by changing the icon in the heirarchy, what I need to do is to duplicate that process in a seperate program so that we can set an alert in a monitoring system we have.
Does anyone know the mechanism that enterprise manager uses to determine that something has gone wrong with replication. If so I would be grateful if you could point me in the right direction. I would prefer it if it can be done in VB.Net but if it has to be some other language then I'm not too bothered.
Thanks
Terry

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

Running this in the distribution database will give you a history of the distribution agents. You can modify the view to look at the other agents wich have there history stored in tables in the distribution database:

select

msdb..sysjobs.name as JobName,

case

when runstatus = 1 then 'Start'

when

runstatus = 1 then 'Succeed'

when

runstatus = 2 then 'In progress'

when

runstatus = 3 then 'Idle'

when

runstatus = 4 then 'Retry'

when

runstatus = 5 then 'Fail'

when

runstatus not in (1, 2, 3, 4, 5) then 'NULL'end as runstatus,

time

, duration, comments from MSdistribution_history (nolock)

inner

join MSdistribution_agents (nolock)

on

MSdistribution_history.agent_id = MSdistribution_agents.id

inner

join msdb..sysjobs (nolock)

on

MSdistribution_agents.job_id = msdb..sysjobs.job_id

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Follow on question .... Once the icon shows there's a problem, how do you find the erroneous flag that remains and set it back ?? I had a problem a while back with some test replication jobs. I've since deleted them, and don't have any remaining problems, but the icon still shows the ugly big red X.

TriggerMe SSChampion Points: 11752 More actions · Answer 3

Sounds like you or someone has attempted to DROP a Publication or a Database that was Published in an unorthodox fashion.

I have seen this before and the way to sort that out is to remove the entire replication process and start again.

Sorry.

Kindest Regards,

Terry Bailey SSC Enthusiast Points: 126 More actions · Answer 4

Thanks for the information, this now allows me to do what I need.

Reference the big red X left over. I had this some time ago after I deleted a broken replication publication and I seem to remember that a complete shutdown of SQL server and restart fixed it, but of course the reason for the X may be different to this case.

Thanks again.