none
Sql Agent service stop notification RRS feed

  • Question

  • 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?

     
    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"
    
    }
    
    }
    

    Wednesday, November 13, 2019 4:02 AM

All replies

  • Hi pdsqsql,

    Could you please take a look at the following links which might help?

    Get SQL Server agent Service Stopped Mail Notification

    Script to send email notification when sql server stops

    In addition, your issue is more related to Windows. I suggest you open a thread in the corresponding forum so that people there will help you more effectively.

    Hope this could help you.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 14, 2019 7:02 AM
  • Thanks Amelia.

    I will check it out the link.

    Saturday, November 16, 2019 3:43 AM
  • Hi pdsqsql,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 19, 2019 9:21 AM