Mailing dba when Sql Server Agent Fails

Hello

I would like to have a script which mails the dba mail box when the sql server agent stops running. I am using the SMTP server for mailing.

I am using the query :
EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

to check the status.

I use the below for mailing :

EXEC master.dbo.xp_smtp_sendmail

@FROM = N'testsql2000@is.depaul.edu',

@TO = N'dvaddi@depaul.edu',

@server = N'smtp.depaul.edu',

@subject = N'Status of sqlserver!',

@type = N'text/html',

@message = @message

How do I change it or write a script get a mail when the sql server agent is not running.

Thanks

Answer this question

Mailing dba when Sql Server Agent Fails

Mastroyani
You would need a script which checks the Agent, becasue there is no shutdown procedure which can be executed if the service becomes unavailable. The Script could either be executed from another Agent or from any scheduled component like AT / Winat of Windows.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---
Davids Learning
I know that , if there are 2 servers first server can monitor the 2nd server agent and vice-versa.

But I am not sure how to work on it or what script to run.

So it would be of great help if anyone can let me know.

Thanks
Mailing dba when Sql Server Agent Fails

Answer this question