SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Find failed SQL jobs with powershell

This weekend I went to SQL Saturday in Auckland. It was nice to interact with other DBAs again.  Kent Chenery (blog | twitter) did a nice session on powershell for n00bs and it got me thinking that I need to start posting some of my powershell stuff up here. When I started my current job I was given a handover document that had a list of standard operating procedures, some links to some useful info, and a "daily morning checks" list. When I first read this list I was amazed. It went something like this:

  1. Log on to server A
  2. Open up management studio
  3. Open up SQL Agent's job
  4. Order by last outcome
  5. Rerun the jobs that failed
  6. Repeat for servers B, C, D....and on

Ouch..this was all done manually. This would take way too long to just "check" to see if these jobs failed. To fix this issue I turned to my good friend - powershell. I came up with little script to hit up all my servers, check the SQL jobs, and mail me if a job failed in the last 24 hours.

#Find Failed SQL Jobs with Powershell
#by Adam Mikolaj

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null;

#let's get our list of servers. For this, create a .txt files with all the server names you want to check.
$sqlservers = Get-Content "C:\DevWork\scripts\computers.txt";

#we'll get the long date and toss that in a variable
$datefull = Get-Date
#and shorten it
$today = $datefull.ToShortDateString()

#let's set up the email stuff
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient("ExchangeServerNameHere")
$msg.Body = “Here is a list of failed SQL Jobs for $today (the last 24 hours)”

#here, we will begin with a foreach loop. We'll be checking all servers in the .txt referenced above.
foreach($sqlserver in $sqlservers)
    #here we need to set which server we are going to check in this loop
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
        #now let's loop through all the jobs
        foreach ($job in $srv.Jobserver.Jobs)
            #now we are going to set up some variables. 
            #These values come from the information in $srv.Jobserver.Jobs
            $jobName = $job.Name;
        	$jobEnabled = $job.IsEnabled;
        	$jobLastRunOutcome = $job.LastRunOutcome;
            $jobLastRun = $job.LastRunDate;
            #we are only concerned about jos that are enabled and have run before. 
            #POSH is weird with nulls so you check by just calling the var
            #if we wanted to check isnull() we would use !$jobLastRun  
            if($jobEnabled = "true" -and $jobLastRun)
                   # we need to find out how many days ago that job ran
                   $datediff = New-TimeSpan $jobLastRun $today 
                   #now we need to take the value of days in $datediff
                   $days = $datediff.days
                       #gotta check to make sure the job ran in the last 24 hours     
                       if($days -le 1 )                    
                            #and make sure the job failed
                            IF($jobLastRunOutcome -eq "Failed")
                                #now we add the job info to our email body. use `n for a new line
                			    $msg.body   = $msg.body + "`n `n FAILED JOB INFO: 
                                 SERVER = $sqlserver 
                                 JOB = $jobName 
                                 LASTRUN = $jobLastRunOutcome
                                 LASTRUNDATE = $jobLastRun"


#once all that loops through and builds our $msg.body, we are read to send

#who is this coming from
$msg.From = “adam@sqlsandwiches.com”
#and going to
#and a nice pretty title
$msg.Subject = “FAILED SQL Jobs for $today”
#and BOOM! send that bastard!

Just set this script up to run every morning as a scheduled task and you can enjoy a nice SQL Sandwich instead of plowing through SQL agent jobs. Next up, I'll do the same job but use the psx extensions. Wonder which is easier...

SQL Sandwiches

Sqlsandwiches is a tool for me to communicate what I have been learning to the SQL community.


Posted by richaugelli on 8 February 2012

Excellent ps script

Posted by john.campbell-1020429 on 15 February 2012

Are you checking the status of the job or are you checking the status of the job steps in the job?  I have run across many reporting jobs that will go the next step on a failure of the current step and as long as the last job step is successful, the job was a success.

I instead check for failed job steps on all of the servers with an SSIS job, or a distributed query

Posted by SQLSandwiches on 16 March 2012

I'm checking with the overall status of the job. If you want to check the status of each step, it's just one more layer.

Posted by R Satyanarayana on 30 May 2012

Good PS script.

We are getting multiple instance jobs status as output in PS Query result pane.

Now we would like to represent the query result pane to be as HTML and it should send us mail. Can you help us how to mofidy....

Posted by paulfelce-755299 on 31 July 2012

I've never used powershell before, but the line

$jobEnabled = "true"

seems to be assigning true to $jobEnabled?

Posted by SQL-DBA-01 on 10 March 2015

Hi ,

Got this error msg..

Exception calling "Send" with "1" argument(s): "Failure sending mail."At line:77 char:1

+ $smtp.Send($msg)

+ ~~~~~~~~~~~~~~~~

   + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

   + FullyQualifiedErrorId : SmtpException

Posted by kumar_r19 on 15 February 2016

Got this error msg..

Exception calling "Send" with "1" argument(s): "Failure sending mail."At line:77 char:1

+ $smtp.Send($msg)

+ ~~~~~~~~~~~~~~~~

  + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

  + FullyQualifiedErrorId : SmtpException

Posted by nkaka009 on 25 October 2016

Nice script , very helpful. :)

Leave a Comment

Please register or log in to leave a comment.