locked
Script to send email notification when sql server stops.. RRS feed

  • Question

  • Hi,

    I am looking for a script through which I can monitor sql server service status and if it down for any reason, I want to send email to Dba team.  How to achieve this? Did anyone has already done similar kind of monitoring?

    Again, I am not looking for any 3rd party monitoring system like SCOM etc.. I looking for a batch file or script outside sql which can solve this.

    Any help would be greatly appreciated.

    Thank you.
    Saturday, June 21, 2014 4:14 PM

Answers

  • You can run a Powershell script like the example below using Task Scheduler on a Windows server other than the SQL instance being monitored.  This script will send an email and terminate (to avoid spam) if the connection fails for any reason.  Start the task again after restarting SQL Server to continue monitoring.

    Try { While(1 -eq 1) { Invoke-Sqlcmd -ServerInstance "YourSqlInstance" -Query "DECLARE @ConnectionTest int;" sleep 5 } } Catch { $smtpServer = "yourcompany.com" $msg = new-object Net.Mail.MailMessage $smtp = new-object Net.Mail.SmtpClient($smtpServer) $msg.From = "windowsServer@yourcompany.com" $msg.ReplyTo = "windowsServer@yourcompany.com" $msg.To.Add("dbaTeam@yourcompany.com") $msg.subject = "Connection to SQL Server Failed" $msg.body = "Connection to SQL Server Failed"

    $smtp.Send($msg) }



    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, June 21, 2014 5:07 PM
  • There are two ways to do it:

    1). There is a job option wherein you can put the alert message and then in schedule option, select to alert you only when Sql Agent Services get restarted. So this way you will come to know when Sql Agent Services got restarted.

    2). From any centralize\third server apart from your database server\host, you can put a job to execute almost around every 5 minutes to check service status and then call a batch file which will call OSQL code to execute sql code for sending alert based on the message content you want to like below:

    Scripts below, even you can tweak more on this as per your environment requirement and even you can merge some scripts to make one out of two or similar, however keeping each separate one helps to track scripts.

    --------------------------------------------------------------------

    First Batch File with Name: ServiceStatusCheck.bat
    with below content

    Echo "Checking Service Status"


    :Query
    FOR /F "tokens=4 delims= " %%A IN ('SC QUERY "XXXXXXXXServiceNametobeCheckedPutHere" ^| FIND "STATE"') DO SET status=%%A
    IF "%status%"=="STOPPED" GOTO Shootout

    :Shootout
    'email for success in first attempt
    'Put path of batch file
    "D:\Script\Successemail.bat"
    Exit


    ----------------------------------------------


    Second Batch file with below content which will call Sql file to send alert: File Name: Successemail.bat


    ECHO "Send Email Alert"

    OSQL -E -S XXXXXXXXX -i "D:\Script\Successemail.sql"

    ECHO "Done"
    Exit

    ---------------------------------------------

    Third Sql File to send email: File Name: Successemail.sql


    Declare @bodyT varchar(200)
    Set @bodyT  =
    'Service was found in stopped status and Date & Time::'
     +replace(convert(varchar(20),GETDATE(), 102),'.','/')
     +'::'
     +convert(varchar(20),GETDATE(), 108)

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name='Profilename',
        @recipients = 'DBADLXXXXX@mycompany.com',
        @body  = @bodyT,
        @subject = 'Service was found in stopped status, Check Details!!'
    GO

    ---------------------------------------------------------------


    Santosh Singh

    • Marked as answer by Manu_vmr Sunday, June 22, 2014 6:43 AM
    Saturday, June 21, 2014 6:26 PM

All replies

  • Which kind of script you are looking for, is it through SQL agent? I dont think we can do this because agent will also be down when SQL services stopped. We may try it through windows task scheduler job.
    • Proposed as answer by SQL_DBA_Sha Friday, January 19, 2018 4:51 PM
    • Unproposed as answer by SQL_DBA_Sha Friday, January 19, 2018 4:51 PM
    Saturday, June 21, 2014 4:57 PM
  • You can run a Powershell script like the example below using Task Scheduler on a Windows server other than the SQL instance being monitored.  This script will send an email and terminate (to avoid spam) if the connection fails for any reason.  Start the task again after restarting SQL Server to continue monitoring.

    Try { While(1 -eq 1) { Invoke-Sqlcmd -ServerInstance "YourSqlInstance" -Query "DECLARE @ConnectionTest int;" sleep 5 } } Catch { $smtpServer = "yourcompany.com" $msg = new-object Net.Mail.MailMessage $smtp = new-object Net.Mail.SmtpClient($smtpServer) $msg.From = "windowsServer@yourcompany.com" $msg.ReplyTo = "windowsServer@yourcompany.com" $msg.To.Add("dbaTeam@yourcompany.com") $msg.subject = "Connection to SQL Server Failed" $msg.body = "Connection to SQL Server Failed"

    $smtp.Send($msg) }



    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, June 21, 2014 5:07 PM
  • There are two ways to do it:

    1). There is a job option wherein you can put the alert message and then in schedule option, select to alert you only when Sql Agent Services get restarted. So this way you will come to know when Sql Agent Services got restarted.

    2). From any centralize\third server apart from your database server\host, you can put a job to execute almost around every 5 minutes to check service status and then call a batch file which will call OSQL code to execute sql code for sending alert based on the message content you want to like below:

    Scripts below, even you can tweak more on this as per your environment requirement and even you can merge some scripts to make one out of two or similar, however keeping each separate one helps to track scripts.

    --------------------------------------------------------------------

    First Batch File with Name: ServiceStatusCheck.bat
    with below content

    Echo "Checking Service Status"


    :Query
    FOR /F "tokens=4 delims= " %%A IN ('SC QUERY "XXXXXXXXServiceNametobeCheckedPutHere" ^| FIND "STATE"') DO SET status=%%A
    IF "%status%"=="STOPPED" GOTO Shootout

    :Shootout
    'email for success in first attempt
    'Put path of batch file
    "D:\Script\Successemail.bat"
    Exit


    ----------------------------------------------


    Second Batch file with below content which will call Sql file to send alert: File Name: Successemail.bat


    ECHO "Send Email Alert"

    OSQL -E -S XXXXXXXXX -i "D:\Script\Successemail.sql"

    ECHO "Done"
    Exit

    ---------------------------------------------

    Third Sql File to send email: File Name: Successemail.sql


    Declare @bodyT varchar(200)
    Set @bodyT  =
    'Service was found in stopped status and Date & Time::'
     +replace(convert(varchar(20),GETDATE(), 102),'.','/')
     +'::'
     +convert(varchar(20),GETDATE(), 108)

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name='Profilename',
        @recipients = 'DBADLXXXXX@mycompany.com',
        @body  = @bodyT,
        @subject = 'Service was found in stopped status, Check Details!!'
    GO

    ---------------------------------------------------------------


    Santosh Singh

    • Marked as answer by Manu_vmr Sunday, June 22, 2014 6:43 AM
    Saturday, June 21, 2014 6:26 PM
  • Thank you very much for the help. I will try both ways. using powershell as well as batch file.
    Sunday, June 22, 2014 6:44 AM
  • Invoke-Sqlcmd -ServerInstance "YourSqlInstance" -Query "DECLARE @ConnectionTest int;"

    Can you please give a example for this command, I tried different format of instance name but always getting as failure notification

    Friday, January 19, 2018 4:53 PM
  • The instance name will be the server name. In the case of a named instance, the server name will be followed by a backslash and the instance name.

    Invoke-Sqlcmd -ServerInstance "YourServerNameHere" -
    Query "DECLARE @ConnectionTest int;"
    Invoke-Sqlcmd -ServerInstance "YourServerNameHere\YourInstanceNameHere" -Query "DECLARE @ConnectionTest int;"


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, January 19, 2018 8:42 PM
  • I tried the same but always receiving alert as connection failed.


    Monday, January 22, 2018 4:55 AM
  • Does this script checks the current availability status of SQL Instance or it checks from the history of event the SQL Instance went down? 

    And where should I get the XXXXXXXXServiceNametobeCheckedPutHere - from Service.msc window?

    Wednesday, January 24, 2018 7:12 AM
  • Did the Invoke-Sqlcmd err? Post the actual script you are using (obfuscating sensitive data as necessary).


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, January 24, 2018 11:41 AM