Finding SQL Server Agent Job Failures

Finding SQL Server Agent Job Failures

Problem
With most, if not all organizations, when executing processes during non business hours it is imperative to know if and when the these processes fail.  At a minimum, full backups are typically executed off hours, but many organizations execute resource intensive batch processes which are critical to the core business for the subsequent day.  With staff members stretched to meet numerous needs, validating that the jobs have executed successfully on a daily basis can be time consuming, but it is often better to know about the failures ahead of time rather than having the users notify IT.  The typical solution to determine if a job fails is to notify a SQL Server operator, but SQL Server Agent mail is not always as reliable as needed.  As such, in this tip we will provide scripts to determine recent SQL Server Agent Job failures.

Solution
Since SQL Server stores the Job information in a set of tables in the MSDB database, it is possible to query these tables to determine the status of the Jobs.  As such, below outlines the Job related tables in the MSDB database:

ID	Purpose	SQL 2000 Table	SQL 2005 Table
1	Primary table for job related information
2	Entry for each step in a specific job
3	Schedule(s) for each job
4	Local or remote servers where the job executes
5	Historical record of the job's execution
6	Current job status, next run date\time, queued date, etc.	Not applicable
7	Historical job step log information for all job steps configured to write to this table	Not applicable

SQL Server 2000 and 2005 Code Snippet

-- Variable Declarations
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT

-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days 
SET @Year = DATEPART(yyyy, @PreviousDate) 
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) 
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)

-- Final Logic
SELECT   j.[name],
         s.step_name,
         h.step_id,
         h.step_name,
         h.run_date,
         h.run_time,
         h.sql_severity,
         h.message,
         h.server
FROM     msdb.dbo.sysjobhistory h
         INNER JOIN msdb.dbo.sysjobs j
           ON h.job_id = j.job_id
         INNER JOIN msdb.dbo.sysjobsteps s
           ON j.job_id = s.job_id
WHERE    h.run_status = 0 -- Failure
         AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC