Description

This custom Ignite alert will run against the monitored instance and determine if the SQL Agent is Started or Stopped. If Stopped, this alert will fire and send the appropriate emails. The alert can be created in Ignite by clicking on Alerts > Manage Alerts and creating a custom alert of type Custom SQL Alert - Single Boolean Return.

SQL statement

CREATE TABLE #status (status VARCHAR(50))
 INSERT #status EXEC master..xp_servicecontrol 'querystate','SQLServerAGENT'
 SELECT CASE status WHEN 'Started' THEN 'TRUE' ELSE 'FALSE' END FROM #status
 DROP TABLE #status

Ignite alert definition