Sql Agent service status notification using windows task scheduler

SQL Server Administration
1

I am trying to setup automatic job using the windows task scheduler to notify when Sql Agent service stopped but somehow i am not getting alert when i am manually stopping to test it out but when i run the job manually then i am getting the email notification. I tried to run manually but automatically didn't work when Sql agent stopped.
I am looking the solution for notification when sql agent service stopped.

I am calling PowerShell script using batch script.

Batch Script: powershell.exe -command C:\Users\ServiceCheck.ps1

Powershell Script (ServiceCheck.ps1):

$servers=get-content "C:\Users\servers.txt"
 
foreach($server in $servers)
{
# go to each server and return the name and state of services 
# that are like "SQLAgent" and where their state is stopped
# return the output as a string
$body=get-wmiobject win32_service -computername $server | 
  select name,state | 
  where {($_.name -like "SQLAGENT*" -or $_.name -like "SQL*AGENT") `
    -and $_.state -match "Stopped"} | 
  Out-String
            
      
if ($body.Length -gt 0)
{
  #Create a .net mail client
  $smtp = new-object Net.Mail.SmtpClient("mail.myinc.com") 
  $subject="SQL Agent is down on " + $server
  $smtp.Send("pds0809@myinc.com", "pds0809@myinc.com", $subject, $body)
  "message sent"
}

}

Thanks for help!

2

Does your task scheduler user have rights to use the mail server?

As all my backups are copied to a central share, I take a different approach.
I look for the latest LastWriteTime of the backups, for each server, once on hour and if it is greater than an hour I send an alert.
This tells me if there is a problem with the agent or the network connections.

3

You can simplify your check for service state:

$servers | % {
    $agentService = Get-Service -ComputerName $_ | ? {$_.Name -like 'SQL*Agent'};

    if ($agentService.Status -ne 'Running') {
        $subject = "SQL Server Agent ($($agentService.Name)) on $($_) is not running"; 

        Send-MailMessage -SmtpServer smtp.server.com `
                         -Subject $subject `
                         -From "agentservicemonitor@somedomain.com" `
                         -To "someuser@somedomain.com";
        }
}

I would probably change this so that I get a list of all services on all servers in a single message. That would require building the body of the message during each iteration - and then sending that body.

4

Task Scheduler user is admin. when i run job manually, it's running fine but not kicking off the job when Sql Agent stop so is it Alert notification even i am picking wrong?
I have to setup trigger/event but not sure which one?

5

Thanks for your response and script.
So i should place the script where?

6

This is just sample code...

There are 2 approaches:

  1. Create a powershell script and place that script on my management server. Create a task scheduler job that is run from an account that has local administrator access to all of the servers being checked.

  2. Create a powershell script and place that script on each server and have it check the local services on that machine.

For the first one - if the server is down your script will fail, so you would have to modify the script to check for that condition and notify for both.

For the second one - if the server is down you wouldn't get any notification. But - hopefully you would have some other monitoring that would tell you when a server is available or not.

We have SCCM that monitors our servers and will notify when the server is down or the services are down. It can also be configured to attempt to restart the services if needed.

If these were my systems - I would be more concerned with why the agent service is stopping. The only time I have seen that situation is when someone did not set the service to start automatically - so it is set to manual and does not start after a server restart. Other than that - the agent service on all of my instances has never just stopped and needed to be manually restarted.

7

Thanks Jeff.
We have configured the services to start automatically and it's not always happening, it's just randomly happening for some servers after patch when rebooting the server but it's not specific server or every time not happening.

8

We also have the same problem after Windows updates are applied. As our network team schedule this overnight I send myself an email at 7am with the times of the last log backup on each server to pick up the problem.

9

ifor -How you have set up?

10

Thanks Jeff.
We don't have Management Server configured so how can i set up throguh Windows Task Scheduler?

11

Create a powershell script - place that script on the server where you will be running it - then create a scheduled task to execute that script on the schedule you want.

12

Thanks Jeff.
If run it with the scheduler then i have to run everyday to check the status as this is just happening randomly.
Is it any way it can trigger when it's automatically stop then create the notification?

Thank You!

13

Yes - you would create the script to check the status of the service, and if the service is not running - start the service and send a notification. If the service is running - then do nothing...

The sample I provided will only send a message if the service is not running. You can easily add code to that section to restart the service.