June 21, 2014 at 10:12 am
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.
June 21, 2014 at 12:12 pm
I don't have a script I can provide but you may want to look at PowerShell for this.
June 22, 2014 at 12:46 am
ok lynn. Thank you.
I will search for powershell script then.
June 23, 2014 at 7:52 pm
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
June 24, 2014 at 8:00 am
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:\>
June 24, 2014 at 8:13 am
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
June 24, 2014 at 8:17 am
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
June 24, 2014 at 8:24 am
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
June 24, 2014 at 12:02 pm
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.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy