Wednesday, May 07, 2008

List failed SQL agent Jobs

This function is used to extract a list of jobs that didn't complete successfully on last execution.
The function takes a single parameter - the SQL server (instance) to query.
This script can be useful in getting a daily overview - especially if wrapped in a loop and run against all servers.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo");
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum");
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

function GetFailedAgentJobs {
    param ([string]$servername="127.0.0.1")
    $srv = new-object('Microsoft.SqlServer.Management.Smo.server') $servername
    $jobs = $srv.jobserver.jobs | where-object {$_.isenabled}
    foreach ($job in $jobs) {
        [int]$outcome = 0
        [string]$output = ""
        ## Did the job fail completely?
        if ($job.LastRunOutcome -ne "Succeeded") {
            $outcome++
            $output = $output + " Job failed (" + $job.name + ")" + " Result: " + $job.LastRunOutcome
        }
        ## Did any of the steps fail?
        foreach ($step in $job.jobsteps) {
            if ($step.LastRunOutcome -ne "Succeeded"){
                $outcome++
                $output = $output + " Step failed (" + $step.name + ")" + " Result: " + $step.LastRunOutcome + " -- "
            }
        }
        if ($outcome -gt 0)    {
            $obj = New-Object Object
            $obj | Add-Member Noteproperty name -value $job.name
            $obj | Add-Member Noteproperty lastrundate -value $job.lastrundate
            $obj | Add-Member Noteproperty lastrunoutcome -value $output
            $obj
        }
    }
}