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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 21, 2003

Nightly Failed Jobs Report

By Gregory A. Larsen

Do you get tired of reviewing each SQL Agent notification nightly to determine which SQL Agent jobs failed? Are there a significant number of SQL Agent job notifications that it takes a while to review each? Here is an alternative that allows you to have a single email report of all job failures.

Why We Created A Single Job Failure Report

Over the past few years, we have implemented a number of SQL Agent jobs in SQL Server. Each job uses the SQL Agent notification process to communicate success or failures to the DBA's. This communication is all done via email. Due to the number of jobs we have, we get lots of emails reporting the successes and failures of our nightly jobs. Every morning, the time to review each of the email notifications to determine success or failure of each SQL Agent jobs was time consuming. In addition, it was easy to get in a habit of quickly deleting the emails without really noticing whether the jobs succeeded or failed. For this reason, we decided to create a process to produce a single job failure report.

What We Did

We decided since the job/step failure information was stored in the MSDB database that we would write a stored procedure called "usp_failed_jobs_report (see "Listing of SP" below) to produce a single report. This stored procedure performs some simple SQL to gather information from the sysjobhistory and sysjobs tables, formats the data into a report, and then emails the report to the our DBAs.

This SP code is run every morning Monday - Friday at 6 am via a SQL Agent job. The SP determines which jobs and job steps have failed since the last scheduled running of this SP. For each failed jobs/job step, this SP produces a single line in the report. The line in the report shows the name of the job and step that failed, plus the date and time of the failure.

Example of Report

Here is an example on the a report produced by this SP.

The following jobs/steps failed since Aug 23 2002 6:00AM 
job step_name failed datetime 
------------------------------------------- --------------------------------- ------------------- 
ADRN1303 TRIGGER TRIGGER FILE EXIST Aug 25 2002 12:15AM 
LOAD EMPLOYEE TABLE LOAD TABLE Aug 25 2002 12:58AM
LOAS EMPLOYEE TABLE (Job outcome) Aug 25 2002 12:58AM 
ADDS load for PROD1 New FTP VALIDATION 1 Aug 25 2002 2:16AM 
ADDS load for PROD1 New FTP VALIDATION 2 Aug 25 2002 2:16AM 

This report shows that "ADRN1303 TRIGGER" job had a step failure, "LOAD EMPLOYEE TABLE" had a step and job failure (the "(job outcome)" step_name indicates a job failure), and "ADDS load for PROD1 New" had two different step failures.

Conclusion

By having a single report, the DBA's in our shop can now review a single email to determine which jobs have failed since the last business day. Having this single report allows a quick method for the DBA's to identify all the jobs and step failures for the past day. By reviewing a single report the DBA's don't have to review each individual SQL Agent notification email to determine which jobs failed.

Listing of SP

CREATE procedure usp_failed_jobs_report as
-- Written by: Greg Larsen
-- Company: Department of Health, Washington State
-- Date: January 3, 2002
-- Description: This SQL Code reports job/step failures based on a data and time range. The
-- report built is emailed to the DBA distribution list.
--
-- Modified 04/12/2002 - Greg Larsen - Modified to support Long running jobs that cross reporting 
-- periods
declare @RPT_BEGIN_DATE datetime
declare @NUMBER_OF_DAYS int
-- Set the number of days to go back to calculate the report begin date
set @NUMBER_OF_DAYS = -1
-- If the current date is Monday, then have the report start on Friday.
if datepart(dw,getdate()) = 2
set @NUMBER_OF_DAYS = -3
-- Get the report begin date and time
set @RPT_BEGIN_DATE = dateadd(day,@NUMBER_OF_DAYS,getdate()) 
-- Get todays date in YYMMDD format
-- Create temporary table to hold report
create table ##temp_text (
email_text char(100))
-- Generate report heading and column headers
insert into ##temp_text values('The following jobs/steps failed since ' + 
cast(@RPT_BEGIN_DATE as char(20)) )
insert into ##temp_text values ('job step_name failed datetime ')
insert into ##temp_text values ('------------------------------------------- --------------------------------- -------------------')
-- Generate report detail for failed jobs/steps
insert into ##temp_text (email_text)
select substring(j.name,1,43)+ 
substring(' ',
len(j.name),43) + substring(jh.step_name,1,33) + 
substring(' ',
len(jh.step_name),33) + 
-- Calculate fail datetime
-- Add Run Duration Seconds
cast(dateadd(ss,
cast(substring(cast(run_duration + 1000000 as char(7)),6,2) as int),
-- Add Run Duration Minutes 
dateadd(mi,
cast(substring(cast(run_duration + 1000000 as char(7)),4,2) as int),
-- Add Run Duration Hours
dateadd(hh,
cast(substring(cast(run_duration + 1000000 as char(7)),2,2) as int),
-- Add Start Time Seconds
dateadd(ss,
cast(substring(cast(run_time + 1000000 as char(7)),6,2) as int),
-- Add Start Time Minutes 
dateadd(mi,
cast(substring(cast(run_time + 1000000 as char(7)),4,2) as int),
-- Add Start Time Hours
dateadd(hh,
cast(substring(cast(run_time + 1000000 as char(7)),2,2) as int),
convert(datetime,cast (run_date as char(8))))
))))) as char(19))
from msdb..sysjobhistory jh join msdb..sysjobs j on jh.job_id=j.job_id
where (getdate() >
-- Calculate fail datetime
-- Add Run Duration Seconds
dateadd(ss,
cast(substring(cast(run_duration + 1000000 as char(7)),6,2) as int),
-- Add Run Duration Minutes 
dateadd(mi,
cast(substring(cast(run_duration + 1000000 as char(7)),4,2) as int),
-- Add Run Duration Hours
dateadd(hh,
cast(substring(cast(run_duration + 1000000 as char(7)),2,2) as int),
-- Add Start Time Seconds
dateadd(ss,
cast(substring(cast(run_time + 1000000 as char(7)),6,2) as int),
-- Add Start Time Minutes 
dateadd(mi,
cast(substring(cast(run_time + 1000000 as char(7)),4,2) as int),
-- Add Start Time Hours
dateadd(hh,
cast(substring(cast(run_time + 1000000 as char(7)),2,2) as int),
convert(datetime,cast (run_date as char(8))))
)))))) 
and (@RPT_BEGIN_DATE < -- Calculate fail datetime
-- Add Run Duration Seconds
dateadd(ss,
cast(substring(cast(run_duration + 1000000 as char(7)),6,2) as int),
-- Add Run Duration Minutes 
dateadd(mi,
cast(substring(cast(run_duration + 1000000 as char(7)),4,2) as int),
-- Add Run Duration Hours
dateadd(hh,
cast(substring(cast(run_duration + 1000000 as char(7)),2,2) as int),
-- Add Start Time Seconds
dateadd(ss,
cast(substring(cast(run_time + 1000000 as char(7)),6,2) as int),
-- Add Start Time Minutes 
dateadd(mi,
cast(substring(cast(run_time + 1000000 as char(7)),4,2) as int),
-- Add Start Time Hours
dateadd(hh,
cast(substring(cast(run_time + 1000000 as char(7)),2,2) as int),
convert(datetime,cast (run_date as char(8))))
)))))) 
and jh.run_status = 0
-- Email report to DBA distribution list
exec master.dbo.xp_sendmail @recipients='greg.larsen@databasejournal.com',
@subject='Check for Failed Jobs - Contains jobs/steps that have failed.', 
@query='select * from ##temp_text' , @no_header='true', @width=150
-- Drop temporary table
drop table ##temp_text
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

» See All Articles by Columnist Gregory A. Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date