Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlpal2007
Posting Yak Master
200 Posts |
 Posted - 2007-10-03 : 10:01:27
|
| Hello AllWe use transactional replication to replication the data between multiple sites. Last week the Log Reader Agents and the Distribution Agents for the multiple publishers failed. Eventhough the sql alert is setup to send email upon replication job failure, we did not get email due to some problem with xp_send mail I guess.Is there anyway to capture the failure of these agents: Log Reader Agent, Queue Reader Agent and Distribution Agent? Is there any table where the log is created on the failures of these agents? I need to monitor that log to see the failure and based on that the vb script will write the failure to the event log and will send email. Or can I create custom alert on failure of these agents?Any help regarding this would be appreciated.Thanks in advance,-P |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-03 : 23:04:20
|
| Try with msdb..sp_help_job to check job status. |
 |
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2007-10-04 : 08:36:52
|
| Thanks for your reply rmiao. I will try your suggestions.One mroe thing I found is there are 3 replication tables in distribution database. If I do the following query I get the error logged records.select * from MSlogreader_history where error_id <> 0select * from MSdistribution_history where error_id <> 0 select * from MSqreader_history where error_id <> 0Can I write insert trigger on these 3 tables to capture the failure based on the insert since these tables contain other records also. If there is insert with the error_id other than zero then there problem on one of the agent at the replication side.My question whould be it is ok to write trigger on the distribution database and would this logic work in all failure cases?Thanks,-P |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-04 : 15:46:51
|
| You can't put trigger on system tables. |
|
|
|
|
|
|
Replication agents failed