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
}
}
}
Wednesday, May 07, 2008
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment