Solved

Powershell 2.0 e-mail when SQL Agent service stopps

Posted on 2013-01-08
22
539 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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
 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

A procedure for exporting installed hotfix details of remote computers using powershell
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …

821 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