Checking for failed SQL Agent jobs should be part of any DBA workplan. Here’s another Powershell script that makes checking the last run outcome easy on multiple SQL Servers. To run this script you need to create a list of your SQL Servers in a text file called sqlservers.txt. Place this text file in your user profile directory, C:\Users\Rhys on my laptop.

# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Get List of sql servers to check
$sqlservers = Get-Content "$Env:USERPROFILE\sqlservers.txt";

# Loop through each sql server from sqlservers.txt
foreach($sqlserver in $sqlservers)
      # Create an SMO Server object
      $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;

      # Jobs counts
      $totalJobCount = $srv.JobServer.Jobs.Count;
      $failedCount = 0;
      $successCount = 0;

      # For each jobs on the server
      foreach($job in $srv.JobServer.Jobs)
            # Default write colour
            $colour = "Green";
            $jobName = $job.Name;
            $jobEnabled = $job.IsEnabled;
            $jobLastRunOutcome = $job.LastRunOutcome;

            # Set write text to red for Failed jobs
            if($jobLastRunOutcome -eq "Failed")
                  $colour = "Red";
                  $failedCount += 1;
            elseif ($jobLastRunOutcome -eq "Succeeded")
                  $successCount += 1;
            Write-Host -ForegroundColor $colour "SERVER = $sqlserver JOB = $jobName ENABLED = $jobEnabled LASTRUN = $jobLastRunOutcome";

      # Writes a summary for each SQL server
      Write-Host -ForegroundColor White "=========================================================================================";
      Write-Host -ForegroundColor White "$sqlserver total jobs = $totalJobCOunt, success count $successCount, failed jobs = $failedCount.";
      Write-Host -ForegroundColor White "=========================================================================================";

The output provided shows jobs with a last run status of “Succeeded” in green and “Failed” in red. A summary is provided for each individual SQL Server.

Checking SQL Agent Jobs with Powershell