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!