August 19, 2009 at 12:14 am
I have 3 PULL subscriber servers with transactional replication , now for Tier 1 we want to give query to check which subscriber has failure among 3 with out the use of replication monitor, I check msrepl_errors tabel but it does not pin point which subscriber has failure as it may be the case if one has issue and other two runs with no issue ,
could somebody tell me how to query to find the failures with subscriber name.
Thanks
October 13, 2009 at 7:08 am
Did you ever find what you were loooking for? I too would like to set up alerts without depending on the monitor.
October 13, 2009 at 1:51 pm
We run this code in SSIS packages against our ditributers and send alerts if we have any issues:
SELECT
MSdistribution_agents.name, MSdistribution_agents.publisher_db,
MSdistribution_agents.publication,
MSdistribution_agents.subscriber_db,
case when runstatus = 1 then 'Start'
when runstatus = 2 then 'Succeed'
when runstatus = 3 then 'In progress'
when runstatus = 4 then 'Idle'
when runstatus = 5 then 'Retry'
when runstatus = 5 then 'Fail'
end as RunStatus,
MSdistribution_history.[time], MSdistribution_history.duration,
MSdistribution_history.delivered_transactions, MSdistribution_history.delivered_commands,
MSdistribution_history.error_id,
MSdistribution_history.comments
FROM
MSdistribution_agents (nolock)
INNER JOIN MSdistribution_history (nolock)
ON MSdistribution_agents.id = MSdistribution_history.agent_id
where MSdistribution_history.error_id > 0
order by MSdistribution_history.[time] desc
Andrew
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply