Looking for script through which I can send email notification when sql server stops..

Question

Post reply

Looking for script through which I can send email notification when sql server stops..

Oracle_91

SSChampion

Points: 14548
More actions
June 21, 2014 at 10:12 am

#396946

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I don't have a script I can provide but you may want to look at PowerShell for this.

Oracle_91 SSChampion Points: 14548 More actions · Answer 2

ok lynn. Thank you.

I will search for powershell script then.

Steve T SSCommitted Points: 1994 More actions · Answer 3

Oracle_91 (6/22/2014)
ok lynn. Thank you.
I will search for powershell script then.

This is quick if you just needed something. Run this as a script with scheduled tasks.

$ServerList = Get-Service -ComputerName <servername> -DisplayName "SQL Server (*"

$BodyMsg = ""

$BodyMsg += "The following SQL servers are currently in Stopped status on: " + $env:ComputerName + "</br>"

ForEach ($Server In $ServerList)

{

If ($Server.Status -eq "Stopped")

{

$BodyMsg += $Server.Name + "/" + $Server.DisplayName + "</br>"

}

Send-MailMessage -SmtpServer <smtpserver> -To <emailaddress> -From <emailaddress> -Subject "Automated Alerts" -BodyAsHtml $BodyMsg

Oracle_91 SSChampion Points: 14548 More actions · Answer 4

Hi Steve,

Thank you for providing the script.

I tried below command to send email to my gmail account but it is failing with below error message. Can anybody tell me why it is failing? Also, i am using powershell 2.0.

PS C:\> Send-MailMessage -To "abc@gmail.com" -From "abc@gmail.com" -SmtpServer "smtp.gmail.com" -UseSSL 587 -Subject "sql server stopped " -body "sql server stopped"

Error message:

Send-MailMessage : A positional parameter cannot be found that accepts argument '587'. At line:1 char:17

+ Send-MailMessage <<<< -To "abc@gmail.com" -From "abc@gmail.com" -SmtpServer "smtp.gmail.com" -UseSSL 587 -Subject "sql server stopped " -body "sql server stopped"

+ CategoryInfo : InvalidArgument: (:) [Send-MailMessage], ParameterBindingException

+ FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.SendMailMessage

PS C:\>

Oracle_91 SSChampion Points: 14548 More actions · Answer 5

Thank you. I was able to fix by myself by doing some google search.

http://stackoverflow.com/questions/1252335/send-mail-via-gmail-with-powershell-v2s-send-mailmessage

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Oracle_91 (6/24/2014)
Thank you. I was able to fix by myself by doing some google search.
http://stackoverflow.com/questions/1252335/send-mail-via-gmail-with-powershell-v2s-send-mailmessage

First, instead of posting a link it would have been better to post your actual fix.

Second, to make it easier for others to check out the link you posted:

http://stackoverflow.com/questions/1252335/send-mail-via-gmail-with-powershell-v2s-send-mailmessage

Oracle_91 SSChampion Points: 14548 More actions · Answer 7

Point noted.

Here is the fix to send email to gmail via Powershell 2.0

$EmailFrom = "notifications@somedomain.com"

$EmailTo = "me@earth.com"

$Subject = "Notification from XYZ"

$Body = "this is a notification from XYZ Notifications.."

$SMTPServer = "smtp.gmail.com"

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)

$SMTPClient.EnableSsl = $true

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("username", "password");

$SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)

Reference link :http://stackoverflow.com/questions/1252335/send-mail-via-gmail-with-powershell-v2s-send-mailmessage

Steve T SSCommitted Points: 1994 More actions · Answer 8

Oracle_91 (6/24/2014)
Hi Steve,
PS C:\> Send-MailMessage -To "abc@gmail.com" -From "abc@gmail.com" -SmtpServer "smtp.gmail.com" -UseSSL 587 -Subject "sql server stopped " -body "sql server stopped"
Error message:
Send-MailMessage : A positional parameter cannot be found that accepts argument '587'. At line:1 char:17

You don't need to supply a port to the -UseSSL parameter.