Solved

Powershell 2.0 e-mail when SQL Agent service stopps

Posted on 2013-01-08
22
535 Views
Last Modified: 2013-01-10
Hi Experts,

I read this article which was really promising, but I am a N00B to Powershell, and I can't seem to troubleshoot my errors.

Essentially, I want a powershell script that runs (send me and e-mail notification)when any of my SQL services stop.

This website sounds like it is so easy, but after several hours of trying, I am not having any luck.

http://sqlish.com/alert-when-sql-server-agent-service-stops-or-fails/#comment-59
Any help would be appreciated

I get the following error when testing:
Missing expression after unary operator '-'.
At line:1 char:2
+ - <<<< Comandlet `Send-MailMessage

+ CategoryInfo          : ParserError: (-:String) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MissingExpressionAfterOperator
0
Comment
Question by:Aaron Goodwin
  • 12
  • 9
22 Comments
 
LVL 11

Expert Comment

by:apathy42
Comment Utility
Check the syntax...it looks like you may have a space after one of your - signs.  You can also check syntax by taking everything within the single quotes and pasting it into a Powershell window, and see if it works.
0
 

Author Comment

by:Aaron Goodwin
Comment Utility
Hi Apathy42,

Thanks for the reply.  As long as I drop the -command and the  single quotes ' '  surrounding the text, the message will send\work from the powershell command prompt.  So how do I get it to run from the windows service recovery is the problem.  if you can get it working with one of your services....anyservice let me know!  much like in the example from the website I was referring to.  I will also try removing spaces after the - signs.

Cheers
0
 
LVL 40

Expert Comment

by:Subsun
Comment Utility
Did you configured the service as shown in screenshot of article?
ServiceAlso please keep in mind that It will send mail in if the service is crashed (Failed)..
0
 

Author Comment

by:Aaron Goodwin
Comment Utility
Hi SubSun, Yes I setup the recovery as illustrated in the article.  I was then trying to test the function by doing an end task from windows task manager to see if the alert would send out.  No alert came through so I've been trying to see if there were other ways I could test the command.  In the article it referred to stopping the service and starting again to test, but that didn't work for me.  Do you know of a way to accomplish what I am trying to do?  Just looking for an alert if the service is
0
 

Author Comment

by:Aaron Goodwin
Comment Utility
Subsun,

Are you able to get an e-mail sent if you fail the agent service, using the methods described in the article?
0
 
LVL 40

Expert Comment

by:Subsun
Comment Utility
Okie.. I have removed some unwanted slash's. Copy below mentioned code to Command line parameters after replacing the following..

Replace suleman@sqlish.com with your TO address
<Mention ServerName> with your server name
<your mail Server> with your SMTP server name
suleman@sqlish.com with your From address
-Command ‘Send-MailMessage -To suleman@sqlish.com -Subject ”<Mention ServerName>:SQL Agent Stopped” -Body ”Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.” -SmtpServer <your mail Server> -From suleman@sqlish.com'

Open in new window

0
 
LVL 40

Expert Comment

by:Subsun
Comment Utility
Yes It should work.. You have PowerShell on the server which you are trying setup this alert, right?
0
 

Author Comment

by:Aaron Goodwin
Comment Utility
Hi Subsun,

Yes SQL server and Powershell  (Ver 2.0) are on the server.
Just a quick question.....where you said to replace <Mention ServerName>.  would I leave it as <awesomeservername> in the greater than and less than symbols....or remove those entirely? same with <SMTPSERVER>

cheers
0
 

Author Comment

by:Aaron Goodwin
Comment Utility
also how do you test this, I've done an end task on the SQLAGENT.exe hoping that would trigger an alert, but nothing at this point.
0
 
LVL 40

Expert Comment

by:Subsun
Comment Utility
Remove <> .. If your mail server Smtp.Mail.com
then
.....-SmtpServer Smtp.Mail.com -From subsun@test.com........
0
 
LVL 40

Expert Comment

by:Subsun
Comment Utility
When you killed SQLAGENT.exe, Did it stop the service?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Aaron Goodwin
Comment Utility
yes it stopped the service
0
 
LVL 40

Expert Comment

by:Subsun
Comment Utility
Run the following code from PowerShell and see if you get a mail (Of course you need to replace the attributes as I mentioned in my previous comment)

Send-MailMessage -To suleman@sqlish.com -Subject ”<Mention ServerName>:SQL Agent Stopped” -Body ”Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.” -SmtpServer <your mail Server> -From suleman@sqlish.com

Open in new window

0
 

Author Comment

by:Aaron Goodwin
Comment Utility
Yes that worked just fine from the PS admin console
0
 
LVL 40

Expert Comment

by:Subsun
Comment Utility
If you are getting mail using above command then it should work fine....Try entering this in Command line parameters..
-command "& '<Copy above mentioned command here>'"

Open in new window


Another option is to save the above command as a .ps1 file. Copy paste above command in to a txt file and save it as "Script.ps1"

Enter the script name (with path of the file) in Command line parameters as follows:..
-command "& 'C:\test\Script.ps1'"
0
 

Author Comment

by:Aaron Goodwin
Comment Utility
tried your idea to save it as a file...same error result

Missing expression after unary operator '-'.
At line:1 char:2
+ - <<<< command
    + CategoryInfo          : ParserError: (-:String) [], Par
    + FullyQualifiedErrorId : MissingExpressionAfterOperator

Even if I just type -command all by itself that error message comes up.  It's like -command is not a recognized cmdlet or something

any ideas?  And thanks for the help too
0
 
LVL 40

Expert Comment

by:Subsun
Comment Utility
Are you adding  -command "& 'C:\test\Script.ps1'" to Command line parameters? If yes how you are getting error? can you post a screenshot?
0
 

Author Comment

by:Aaron Goodwin
Comment Utility
Hi Subsun,

Yes I added the -command "& 'C:\Automation\Service_down_notify.ps1'" to the command line Parameters and killed the SQLAGENT.exe process with task manager to test.  No e-mail was sent....nothing happened.

The only way I am able to get errors that I have posted is by running commands in the PS window.  I've been trying to figure out if it is a syntax issue on my end, or if this just doesn't work on my system.  Have you been able to get notification using this method for any service yourself?
0
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
Comment Utility
In PS window you just run C:\Automation\Service_down_notify.ps1 for testing..
0
 

Author Comment

by:Aaron Goodwin
Comment Utility
works fine running
& "C:\automation\Service_down_notify.ps1"
from the PS window
0
 

Author Comment

by:Aaron Goodwin
Comment Utility
WHOOO HOOOO!!!

Got it!
got rid of the -command and left this in the Command Line Paramaters.
& "C:\Automation\Service_down_notify.ps1"
e-mail fired and the service restarted after killing SQLAGENT.exe in the task manager.  You rock!  Thanks so much for the help.
0
 

Author Closing Comment

by:Aaron Goodwin
Comment Utility
thanks for the help, works like a charm now
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

"Migrate" an SMTP relay receive connector to a new server using info from an old server.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now