Failed SQL Server Agent Jobs

By:   |   Comments (6)   |   Related: > SQL Server Agent


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, March 22, 2019 - 10:53:13 AM - Alexandre Back To Top (79372)

 Thank you very much for the tip. Saved me.

Thursday, November 27, 2014 - 4:04:08 AM - chathura Back To Top (35423)

Good article...really good.

Friday, October 5, 2012 - 2:32:29 PM - Jeremy Kadlec Back To Top (19804)

Martyn,

Thank you for the feedback.  This looks good.  I hope to update this tip in the near term with some additional items.

Thank you,
Jeremy Kadlec

Wednesday, October 3, 2012 - 9:15:01 AM - Martyn Back To Top (19773)

Sorry, just a further point that's interesting:

With the alteration I've suggested the query optimiser applies more weight to the final clusted index seek(sysjobs.clust [j] 17% as opposed to 11% with the original) and less wieght on the initial clustered index scan (sysjobhistory.clust [h] 77% opposed to 83% in the original). It also estimates the number of rows much more efficiently, actual is 36, estimaged is 36.642, very close. In the original with the variable, estimaged is 11.4.

Perhaps an agument against variable :)

Thanks again, been fun and I've got it stored to use daily!

 

 

Wednesday, October 3, 2012 - 8:46:44 AM - Martyn Back To Top (19772)

Great tip and just what I was looking for, many thanks!

I made a couple of tweaks for ease or reading, would you proivde feedback please.

1st, the dates:

-- 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)

print @finaldate

--test convert in one
print convert(varchar(30), DATEADD(dd, -7, GETDATE()), 112)

Both return the same and work within the code so I altered it and all is good... I think :)

SELECT h.server                                          AS [SQL Server],
       j.[name]                                          AS [Job Name],
       s.step_name                                       AS [Historic Step Name],
        h.step_id                                         AS [StepID],
       h.step_name                                       AS [Current Step Name],
        LEFT(h.run_date, 4) + '-'
       + Substring(Cast(h.run_date AS VARCHAR(8)), 5, 2)
       + '-' + RIGHT(h.run_date, 2)                      AS [Run Date],
       LEFT(h.run_time, 2) + ':'
       + Substring(Cast(h.run_time AS VARCHAR(8)), 3, 2) AS [Run Time],
       h.sql_severity                                    AS [Severity],
       h.message                                         AS [Message]

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 > CONVERT(VARCHAR(30), Dateadd(dd, -7, Getdate()), 112)

ORDER  BY h.instance_id DESC,
          h.run_date DESC,
          h.run_time DESC 

 

Thursday, September 6, 2012 - 9:41:02 AM - Patel H Back To Top (19408)

Thanks Jeremy...nu muss no fuss...straight to point...cheers














get free sql tips
agree to terms