locked
Mailing dba when Sql Server Agent Fails RRS feed

  • Question

  • 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

    Tuesday, September 5, 2006 5:23 PM

All replies

  • 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
    ---
    Wednesday, September 6, 2006 3:12 PM
  • 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

     

    Friday, September 8, 2006 3:38 PM