question

David Wimbush avatar image
David Wimbush asked

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.
sql-agent
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image
Shawn_Melton answered
PowerShell. [Monitoring SQL Server Agent with Powershell][1]. This uses Get-WMIObject and the -ComputerName parameter that will allow you to remotly hit each server name that you pass to it. Then at the end of the post he shows the setup for it to send you or your team an email notification. You can find other scripts out there that may do more, like importing the results into a database or something. You want to do something even cooler like say receive a Windows notification ballon when the server goes down, check Laerte Junior [post][2] on a module he wrote. [1]: http://www.sqlteam.com/article/monitoring-sql-server-agent-with-powershell [2]: http://shellyourexperience.wordpress.com/2011/07/01/hey-powershell-eventing-can-you-notify-me-when-sql-server-service-stop/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
@Kev - in windows task scheduler. Very basic, but would work.
2 Likes 2 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
where would you schedule the job?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
sorry didn't read that properly - was thinking SQL Agent job!!!!
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Hmm, interesting one, seeing as the Agent is the process that generates and raises the alerts.....maybe you could look into Extended Events?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image Shawn_Melton commented ·
I try to upvote this but get anonymous users cannot vote, but I'm logged in to comment on it???
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Strange. Just give it two votes when it starts working again :)
0 Likes 0 ·
visionabove avatar image
visionabove answered
With powershell it's possible to send email on service failure [Email Alert using powershell][1] [1]: http://etlexperts.blogspot.com/2014/02/email-alert-using-powershell-when.html
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.