locked
SQL Agent Service Alert RRS feed

  • Question

  • Hi, I would like to setup an alert if the SQL Server Agent Service stopps running. We had this problem recently, Agent service stopped for some reason and none of us noticed. We noticed only after a week, when there was already enough damage. Any CMDSHELL scripts or other ideas?

     

    Thank you

    Wednesday, July 27, 2011 3:17 PM

Answers

All replies

  • I assume that you have configured the SQL Server Agent service to restart if it fails unexpectedly?

    See SQL Server Agent Properties.

    You could then create a SQL Server Agent Jobs that fires on the SQL Server Agent Service startup, to monitor for the eventuality. 

    Alternatively you could monitor the service using PowerShell as described in the following post:

    http://www.sqlteam.com/article/monitoring-sql-server-agent-with-powershell


    John Sansom | SQL Server DBA Blog | Twitter
    • Proposed as answer by ManoharPunnaMVP Wednesday, July 27, 2011 4:09 PM
    • Marked as answer by SQLmaddy Thursday, July 28, 2011 12:54 AM
    Wednesday, July 27, 2011 3:24 PM
  • To add to John's post.  You can check using xp_servicecontrol as, "xp_servicecontrol querystate,MSSqlserverAgent" but I'm not a fan of XP operations and would go outside to monitor the service since this is idealy a service monitoring task at the OS level.  In that case I'd use the command, "SC" to return to a file the state on a schedule task or some other monitoring method that fits in your standards.

    so for a named instance it would be..."sc query "SQLAgent$SQLR2" > c:\state_agent_service.txt"


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, July 27, 2011 3:28 PM
  • Thanks for the response. Yeah it is configured to restart, but it didn't happen. We have already have the alert in case if the agent service starts. But from now on we would like to schedule some job that runs every 10 mins to monitor the SQL Agent. I will look into the powershell you sent me, Thank you
    Wednesday, July 27, 2011 3:32 PM
  • Try this solution :) .Make sure you monitor your agent through other instance because if the agent goes down the alerts and jobs under it will not fire and you will not get the alert .So if you configure it on server X you make sure you are monitoring server Y whether its SQL or its Agent ...And its for 2005 and above .In @wmi_namespace you will see a DOT (.) , change it with the server you want to monitor .make sure SQL is running under domain account and both servers can see each other ..

    Also before executing this script make this small change :

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,
      @alert_replace_runtime_tokens=1
    GO

    Then execute this script

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_alert @name=N'SQL Agent down Alert on server <Server name>',
      @message_id=0,
      @severity=0,
      @enabled=1,
      @delay_between_responses=0,
      @include_event_description_in=0,
      @category_name=N'[Uncategorized]', 
      @wmi_namespace=N'\\.\root\CIMV2',
      @wmi_query=N'select * from __instanceDeletionEvent within 300 where targetinstance isa ''Win32_Process'' and targetinstance.Name=''sqlAgent90.exe''',
      @job_id=N'00000000-0000-0000-0000-000000000000'

    Later you can use database mail to get the alert notifications ..if you have issues configuring it , let us know ...

    This will monitor the other agent every 5 mins ( HINT : within 300 )


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Wednesday, July 27, 2011 4:42 PM
  • Thanks John, I tried the link you shared. It is working. 

     

     Hi Abhay, SInce I didn't try your code since it involves running the job from Agent Service. But its nice to know for knowledge. Thanks both to ONPNT and you for reply.

    Thursday, July 28, 2011 1:01 AM