Failed SQL Server Agent Jobs

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:

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

SQL Server 2000 and 2005 Code Snippets

SQL Server 2000 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

SQL Server 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
           AND h.step_id = s.step_id
WHERE    h.run_status = 0 — Failure 
         AND h.run_date > @FinalDate 
ORDER BY h.instance_id DESC

Next Steps

It is imperative to check your jobs on a daily basis, so determine the best means for your organization to automate that process.
For advanced job chaining, job management, alerting, performance metrics research the Job Management applications in the market place.
If you are not taking full advantage of SQL Server Agent, stay tuned for additional MSSQLTips on the topic. For now, check out the following tips:
- <– SQL Server Security Note –> SQL Server Agent Fixed Database Roles
- SQL Server 2005 Exposed – Log File Viewer

Jeremy Kadlec

Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.

Leave a ReplyCancel Reply