Can I set up an alert when the SQL Agent service stops?
I just got caught out by an exception in sqlagent.exe that stopped the SQL Agent service. In the Services configuration you can get it to try and restart but I'd like to know there was a problem. There is an option to run a program so I guess I could write a little app that sends me an email but that's a bit hacky. Does anyone know of a way to get an alert when the service fails or stops, please? I've got several servers with SQL 2008 R2 Standard or Workgroup Edition, and the OSs are Windows Server 2003 or 2008.
I have a Network Operations Center that monitors basic stuff on my servers. Disk space, CPU, memory utilization, etc. I also have them monitor services such as sql server, sql server agent, etc. If you don't have such an environment you can schedule a job that polls every few minutes in task scheduler that checks for the service running, if it isn't then send you an email or start the service.
In the computer management program on the services panel, you can configure a service to run a program when it fails. If you open the properties window for the SQL Agent service and go to the recovery tab, you can specify which actions the service takes on the first, second, and subsequent failure. On this tab, you set the response for failure to "Run a Program". Enter "PowerShell" in the "Program" box and a powershell email command in the "Command line parameters" box - -Command `Send-MailMessage -To ENTER-YOUR-DESTINATION-EMAIL-ADDRESS-HERE -Subject \"SQL Agent Crashed\" -Body \"Find out why\" -SmtpServer ENTER-YOUR-MAIL-SERVER-HERE -From ENTER-YOUR-SOURCE-EMAIL-ADDRESS-HERE` and click OK Kill the agent service with task manager, and you'll get the email you configured. The nice thing about this approach is that it's slightly more proactive than a polling solution. I would still implement a polling solution, as you never know when a service might simple become unresponsive rather than fully crashing.