Implementation: Get a mail alert via powershell when SQL server agent service gets stopped.
Scenario: When SQL server agent service gets stopped,its dependence jobs,DR,operators,DB mail,replication failed to succeed.In these case,you can have a mail alert which will notify whenever agent service gets stopped by triggering mail to provided mail id via windows powershell.
Infrastructure:The below query/steps have been run/tested successfully on SQL server 2005,2008,2008 R2 and 2012.
Steps:
First write the Powershell code below in notepad and save in location say F:\ drive with name “WorkingPowershellScripts.ps1” without quote.
#Create a .net mail client
#Get the Machine Name
$SrvrName=$env:computername
#Put the recepient name.Put only one recepient here.
$recpts = “dharmesh.mishra@cloverinfotech.com”
#Enter your SMTP mail server name in place of email.cloverinfotech.com
$smtp = new-object Net.Mail.SmtpClient(“email.cloverinfotech.com”)
$subject=”Microsoft SQL Agent Service is down on Server” + ” ” + $SrvrName
#Put the from address
$from=”dharmesh.mishra@cloverinfotech.com”
$body=”Dear Team,`n`nPlease Check the SQL Agent Service Status On”+ ” “+ $SrvrName+”.”+” For any assistance plz mail us on dharmesh.mishra@cloverinfotech.com “+”`n`nThanks and Regards,`nDharmesh Mishra`nClover TSG-SQL Consultant`nCell No 7506268860”
foreach ($recpt in $recpts){
$msg = New-Object system.net.mail.mailmessage
$msg.From = $from
#Here you can add as many as mail id example
#$msg.To.add(“puneet.jadhav@cloverinfotech.com”)
$msg.To.add(“dharmesh.mishra@cloverinfotech.com”)
$msg.Subject = $subject
$msg.Body = $body
$smtp.Send($msg)
}
Copy the above code from below location.
https://app.box.com/s/zhwsdrfi16vedm36v749z631kmrg4ezz
Step 2:
Create one batch file to run above .PS1 file
Open notepad and copy the code below and save in drive say F:\ with name “RunPSByCMD.bat” with quote.
@ECHO OFF
SET ThisScriptsDirectory=F:\
SET PowerShellScriptPath=F:\WorkingPowershellScripts.ps1
PowerShell -NoProfile -ExecutionPolicy Bypass -Command “& {Start-Process PowerShell -ArgumentList ‘-NoProfile -ExecutionPolicy Bypass -File “”%PowerShellScriptPath%””‘ -Verb RunAs}”;
Copy the above code from below location.
https://app.box.com/s/lrjuiiwkhtkre4dbwdzfte90b2lpdj8z
Step 3:
Now go to task scheduler and create basis task. Follow the screen shot below.
Create a basic task.
Name the task and description(Optional)
