Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted December 1, 2016

Identifying Last Night’s SQL Agent Job Failures

By Greg Larsen

The first thing I do in the morning at work is to scan my email looking for nightly failed SQL Agent jobs.  Not only does my inbox have all the SQL Agent Job failures, but it also has lots of successful job notifications and alert email messages.  Finding the nightly job failures is just one of my morning DBA rituals.   It is fairly easy to scan all the email with a number of creative search criteria.  But I would like a more automated approach.

In looking for a more automated approach I developed a script that will find and report all job failures.  I can use my scripted solution to run against the set of servers in our Central Management Server environment to get a single report across all of our SQL Server instances.   The script I developed reads SQL Agent job history information from the msdb database. 

By joining a set of job history tables together I was able to quickly produce a single failure report.  The report not only listed which jobs failed, but in addition also includes the step failure information.  Having this information allows for a quick triage of the job failures.  Having this triage information lays the ground work for morning firefighting of nightly job failures. 

Here is the script I cobbled together to help with my morning SQL Agent job failure triage task:

DECLARE @OneDayAgo INT = CONVERT(INT,DATEADD(dd,-1,getdate()),112);
SELECT   j.name,  
         CONVERT(date,cast(h.run_date as varchar(8)),111) run_date,  
               STUFF(STUFF(RIGHT(CAST(h.run_time + 1000000 as varchar(8)),6),3,0,':'),6,0,':') AS run_time,
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 =-- did the job fail
         AND h.run_date > @OneDayAgo 
ORDER BY h.instance_id DESC;    

See all articles by Greg Larsen

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM