August 2016 – ADsql Is Leading

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.