solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Failed SQL Server Agent Jobs

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (4)   |   Related Tips: More > 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, -7GETDATE()) -- 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 -- 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, -7GETDATE()) -- 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 -- Failure
         
AND h.run_date @FinalDate
ORDER BY h.instance_id 
DESC

 

 

Next Steps



Last Update: 10/12/2006


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, September 06, 2012 - 9:41:02 AM - Patel H Read The Tip

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


Wednesday, October 03, 2012 - 8:46:44 AM - Martyn Read The Tip

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 

 


Wednesday, October 03, 2012 - 9:15:01 AM - Martyn Read The Tip

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!

 

 


Friday, October 05, 2012 - 2:32:29 PM - Jeremy Kadlec Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.