List failed SQL server Jobs with Powershell

This post explains how to list failed SQL Server Jobs , on multiple SQL Server Instances and output the results to a HTML file.

 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, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), 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

 

See Also

SQL Server – Send email using Powershell

SQL Agent Jobs – Schedule in seconds

 

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

SQL Agent Jobs – Schedule in seconds

1)   How can I execute a SQL Server Agent Job every few seconds? Administrators try to use SQL Server Agent as a real-time scheduler  - and will attempt to exploit the in- build scheduler functionality.

2)  I wouldn’t recommend SQL Server Agent for sub-minute frequency scheduling. There are  better suited tools for this task. But , if SQL Server Agent is the only available option, use the stored procedures:

              msdb.dbo.sp_add_jobschedule or
              msdb.dbo.sp_update_jobschedule.

 The @freq_subday_type  is associated with msdb..sysjobschedules .

 The msdb..sysjobschedules  table allows the extra value of 0x2  (seconds).This example script updates a schedule to run every 15 seconds

 

GO
EXEC msdb.dbo.sp_update_schedule @schedule_id=4, 
		@freq_subday_interval=15,
            @freq_subday_type=2
GO

See Also

SQL Server agent fixed roles

SQL Server agent fixed roles

During a  security audit on SQL server 2005 server,  I discovered an administrator had assigned the permission "sysadmin" to a logon.
He explained to me the reason : when they were on SQL Server 2000 it was required to allow a logon read\execute rights on SQL Agent Jobs.
We've now changed this policy to use the SQL Server Agent fixed roles - which allows a more detailed role assignement to logons.
It's worth noting these are mdsb database fixed roles
The choices are:
1) SQLAgentUserRole
2) SQLAgentReaderRole (includes SQLAgentUserRole)
3) SQLAgentOperatorRole (includes SQLAgentUserRole and SQLAgentReaderRole)
We've implemented by setting up a separate AD group , adding relevant Windows users - and then adding the logon to the server. That way we can maintain a tighter control on who can view\execute SQL Server Agent Jobs
Author: Jack Vamvas (http://www.sqlserver-dba.com)

Powershell - List Failed SQL Server Jobs

Running the code below within the Powershell interface will list all SQL Server jobs failed
on 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