solving sql server problems for millions of dbas and developers since 2006

Failed SQL Server Agent Jobs

By: Jeremy Kadlec | Read Comments | Print

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.

Related Tips: More

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

Related Tips: More | Become a paid author

Last Update: 10/12/2006

Share:

Free SQL Server Learning

Comments and Feedback:

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!"

Time-strapped database professionals use SQL Monitor to look after their servers. Try it online.

What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check starting at $995.

Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise.

Join the over million SQL Server Professionals who get their issues resolved daily.

Free web casts for DBAs and Developers on Performance Tuning, Development, Administration and more....