Task Scheduler Job set up for Sql Server Agent

• pdsqsql

  Old Hand

  Points: 358

  More actions

  November 12, 2019 at 9:57 pm

  #3697189

  Hello,

  I am trying 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 but when i run the job manually then i am getting the email notification.

  I think it should be right windows event trigger setting bu i am not sure which one i should setup the triggering action.

  I need notification when sql agent service stopped.

  If someone can help me out?

   

  Thanks in advance!

• pdsqsql

  Old Hand

  Points: 358

  More actions

  November 12, 2019 at 10:41 pm

  #3697201

  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"

  }

  }