List failed SQL server Jobs with Powershell

06 February,2012 by Jack Vamvas

This post explains how to list failed SQL Server Jobs , on multiple SQL Server Instances and output the results to a HTML file. Excecute the  Powershell script  as part of daily reporting

 It is only one extra step to send this file as an attachment.

The example script , returns failed SQL Server Agent Jobs within the last 48 hrs, for a SQL Server Instance.

 

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
$instancepath=$basepath + "\instances.txt"
$outputfile="\logs\sql_server_health_sqlserver_jobs_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile
#invoke stylesheet
. .\modules\stylesheet.ps1
 
$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{

$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "SELECT sjh.server,sj.name, sjh.message, sjh.run_date, sjh.run_time , ‘http://www.sqlserver-dba.com/contact-me/’ as Explain
FROM msdb..sysjobhistory sjh
JOIN msdb..sysjobs sj on sjh.job_id = sj.job_id
JOIN (SELECT job_id, max(instance_id) maxinstanceid 
FROM msdb..sysjobhistory 
WHERE run_status NOT IN (1,4) 
GROUP BY job_id) a ON sjh.job_id = a.job_id AND sjh.instance_id = a.maxinstanceid 
WHERE    DATEDIFF(hh, msdb.dbo.agent_datetime(run_date,run_time), GETDATE()) <= 48
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
 
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle | Set-Content $outputfilefull

 

Read More on Powershell email and scheduling SQL Agent jobs

SQL Server – Send email using Powershell

SQL Agent Jobs – Schedule in seconds

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on List failed SQL server Jobs with Powershell


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer