SQL Server - Powershell - List Failed SQL Server Jobs

14 August,2008 by Jack Vamvas

Running the code below within the Powershell interface will list all SQL Server jobs failed
on SQL Server instances (SQL SERVER 2000 & SQL SERVER 2005) listed in the "C:\Instances.txt" document.
An example of the lists in that document is:

SERVER1\INST1
SERVER2\INST2
etc

Currently the sql code will list jobs failed within the last 2 days. Configure to your requirements.

To run , wither copy and paste straight into a Powershell  cmdlet which is a series of commands, usually more than one line, stored in a text file with a .ps1 extension. 

-----CODE STARTS-----------------------------------
foreach ($svr in get-content "C:\Instances.txt"){
    $svr
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=msdb;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "SELECT sjh.server,sj.name, CONVERT(VARCHAR(30),sjh.message) as message , sjh.run_date, sjh.run_time
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(dd,CONVERT(VARCHAR(8),sjh.run_date), GETDATE()) <= 2"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize
}
-----------------CODE ENDS--------------------------

 See Also

SQL Agent Jobs – Schedule in seconds

Powershell Scripts for DBA


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 SQL Server - Powershell - List Failed SQL Server Jobs


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