Solved

Powershell 2.0 e-mail when SQL Agent service stopps

Posted on 2013-01-08
22
538 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
ID: 38756411
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
ID: 38756488
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
ID: 38756518
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
ID: 38760204
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
ID: 38760255
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
ID: 38760274
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
ID: 38760288
Yes It should work.. You have PowerShell on the server which you are trying setup this alert, right?
0
 

Author Comment

by:Aaron Goodwin
ID: 38760461
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
ID: 38760498
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
ID: 38760503
Remove <> .. If your mail server Smtp.Mail.com
then
.....-SmtpServer Smtp.Mail.com -From subsun@test.com........
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38760507
When you killed SQLAGENT.exe, Did it stop the service?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:Aaron Goodwin
ID: 38760509
yes it stopped the service
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38760537
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
ID: 38760608
Yes that worked just fine from the PS admin console
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38760636
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
ID: 38762578
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
ID: 38762583
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
ID: 38762670
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
ID: 38762688
In PS window you just run C:\Automation\Service_down_notify.ps1 for testing..
0
 

Author Comment

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

Author Comment

by:Aaron Goodwin
ID: 38763142
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
ID: 38763149
thanks for the help, works like a charm now
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Synchronize a new Active Directory domain with an existing Office 365 tenant
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 walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…

911 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

19 Experts available now in Live!

Get 1:1 Help Now