DaveWentzel.com            All Things Data

ListFailedJobs

 

  1. ## ===================================================================== 
  2. ## Title       : List-MSSQL-FailedJobs 
  3. ## Description : Lists failed SQL Server jobs using SMO 
  4. ## Input       : -serverInstance <server\instance> 
  5. ##                   -verbose  
  6. ##                   -debug     
  7. ## Output      : List failed jobs 
  8. ## Usage            : PS> . List-MSSQL-FailedJobs -serverInstance MyServer -v -d 
  9. ## Change log  : 
  10. ## ===================================================================== 
  11.  
  12. param 
  13.       [string]$serverInstance="(local)"
  14.     [switch]$verbose
  15.     [switch]$debug 
  16.  
  17. function main() 
  18.     if ($verbose) {$VerbosePreference = "Continue"
  19.     if ($debug) {$DebugPreference = "Continue"
  20.     List-MSSQL-FailedJobs $serverInstance 
  21.  
  22. function List-MSSQL-FailedJobs($ServerInstance
  23.     trap [Exception]  
  24.     
  25.         write-error $("TRAPPED: " + $_.Exception.Message); 
  26.         continue
  27.     
  28.  
  29.     #Load SMO assemblies 
  30.     [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo"
  31.     [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum"
  32.     [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo"
  33.  
  34.     $namedInstance = new-object('Microsoft.SqlServer.Management.Smo.server') ($serverInstance
  35.      
  36.     $jobs = $namedInstance.jobserver.jobs | where-object {$_.isenabled
  37.  
  38.     # Process all SQL Agent Jobs looking for failed jobs based on the last run outcome 
  39.     foreach ($job in $jobs)  
  40.     
  41.         [int]$outcome =
  42.         [string]$output = "" 
  43.      
  44.         # Did the job fail completely? 
  45.         if ($job.LastRunOutcome -ne "Succeeded")  
  46.         
  47.             $outcome++ 
  48.             $output = $output + " Job failed (" + $job.name + ")" + " Result: " + $job.LastRunOutcome 
  49.         
  50.          
  51.         # Did any of the steps fail? 
  52.         foreach ($jobStep in $job.jobsteps)  
  53.         
  54.             if ($jobStep.LastRunOutcome -ne "Succeeded"
  55.             
  56.                 $outcome++ 
  57.                 $output = $output + " Step failed (" + $jobStep.name + ")" + " Result: " + $jobStep.LastRunOutcome + " -- " 
  58.             
  59.         
  60.          
  61.         if ($outcome -gt 0)     
  62.         
  63.             $obj = New-Object Object 
  64.             $obj | Add-Member Noteproperty name -value $job.name 
  65.             $obj | Add-Member Noteproperty lastrundate -value $job.lastrundate 
  66.             $obj | Add-Member Noteproperty lastrunoutcome -value $output 
  67.             $obj 
  68.         
  69.     
  70.  
  71. main 

Add new comment