The SQL Agent is our friend!
Setting up SQL Server Agent to automate jobs and alerts is a great idea.
It is always better to learn about a failing job or system prior to end users so we, as DBAs, can try to deal with it.
But what happens when the SQL Agent stops and we can’t get those alerts?
The problem with a lot of alerting systems is that they are either constantly pinging your server and service, or they tell you after the fact that a service failed as it comes back online (hopefully).
Who Alerts us when the SQL Agent itself fails?
That’s a good question. By default there is no alert of the agent failing, except something written to the log. Unless you are using some sort of log or service monitoring solution, you don’t get told things aren’t running. At least you are not told until your phone or email blows up with angry end users since their reports or data loads are not working.
It would be nice to have something alert you when the agent itself stops wouldn’t it?
Especially if you could get that capability with tools you already have. Always nice to not have to pay for extra tools and overhead when you don’t have to right?
What to do?
All the services running on your server have a recovery tab. This tab has settings that tell Windows what to do if the service fails. The options are: Take No Action (the default), Restart the Service, Run a Program, or Restart the Computer. Some drastic, some not so much.
Restart the service is a nice option, and what’s cool is that you actually have the ability to try that first, then react if the service fails again, and yet another option if the service continues to fail.
For our situation, the SQL Agent turning off and we not being aware of it, I want to focus on the Run a Program option.
What Program to run?
That’s easy. The every powerful PowerShell to the rescue.
Yeah, that’s right, you can run a PowerShell script if your service fails for some reason.
What Script to use? How about one to email us an alert that the service has stopped?
Here is the relatively generic script I’ve got. You will need to update it to use your email credentials, but otherwise it should be pretty straight forward.
$arrService = Get-Service -name "SQLSERVERAGENT"
if ($arrService.Status -ne "Running"){
$service = Get-WmiObject win32_service -computername $env:computername | select name,state | where { $_.name -like "SQLSERVERAGENT"} | out-string
# Specify a sender email address
$emailFrom = $env:computername + " <[email protected]>"
# Specify a recipient email address
$emailTo = "[email protected]"
# Put in a subject line
$subject = $env:computername + " SQL Server Agent is not running!"
# Add the Service state from line 6 to some body text
$body = $service + "If the SQL Agent service is not running scheduled jobs, alerts and backups cannot be performed."
# Put the DNS name or IP address of your SMTP Server
$smtpServer = "<SMTP server IP or name>"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
# This line pieces together all the info into an email and sends it
$smtp.Send($emailFrom, $emailTo, $subject, $body)}
Just save this file as <somename>.ps1 and you are ready to use it in PowerShell.
Now, you know not to trust any code you find online unless you know what it does so let’s go through this script step by step.
The first line collects information about the SQLSERVERAGENT service and stores it in a variable named $arrService
Then we check the status of the service, and if it is not running we are going to send our email alert via SMTP.
We get the name and status of the SQLSERVERAGENT status and pass that to the email variables required. First the From section where we put the servername in first so we know exactly where the agent has failed. Next is who we are sending the alert to. A group email is a good idea. We add a subject line consisting of the servername and “SQL Server Agent is not running!” along with some body text to let everyone know how serious this could be. Finally we set the SMTPServer to use and create a new object to use and call the send operation passing in our email parameters we made earlier.
How to use this file
Now that we have our PS1 file that will send the email alert, but we need to have the job run when the service fails. To set this open up services and right click on the SQLServerAgent service you wish to add the failure alert to and select properties. Navigate to the Recovery tab and set one of the failure actions to “Run a Program” I tend to choose it as the first failure action as I like to look at why my service failed before I just restart it. You can attempt to restart first and then run the program on if it fails again by setting the second failure to run a program. The choice is yours!
Once set to Run A program ,the box labeled “Run Program” activates. Here you will enter the path to the PowerShell executable, and then add the command to run our script.
In general the PowerShell executable for V1.0 can be found here: C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe
(Version 1.0 is fine for running this script, but higher version can be used too)
The command will look like: -Command <path to ps1 fie you saved>
So in my case, on one of my test machines, it looks like this:
That’s it!
Now your SQL Server agent not only tells you when jobs fail and alerts you when other problems pop up. It also tells you when it is having issues itself.
Reminder: This isn’t just for the SQL Server Agent. You can use this to send an alert when any service fails. You can even use the same base concept to do more than just send an email. The possibilities are endless!