Find SQL Agent Jobs that Failed

Last week we looked at a UDF that alerted us if a backup hadn't run recently. This week we'll take a look at failure in another of SQL Server's services Jobs.

Jobs are run by SQL Server Agent and can be very helpful in automating database administration tasks. SQL Server uses them itself to implement database maintenance plans and replication agents, so you might see several jobs in the job list in enterprise manager, even if you hadn't' added any your self.

I use jobs for the usual purposes and for automating some database-to-database synchronization tasks that are critical to the application that I'm working on. The job is made up of custom code that moves data from SQL Server to DB2 in a manner similar to replication but requiring enough manipulation of the code that standard solutions such as heterogeneous replication and LEI wouldn't work. The job and some others are critical to the success of the application.

udf_Job_FailedTAB shows just the jobs that failed by examining msdb's sysjobs and sysjobservers tables and listing jobs where sysjobservers.last_run_outcome=0. Pretty simple really, if you know where to look. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function dbo.udf_Job_FailedTAB (

) RETURNS TABLE
/*
* Lists jobs that failed the last time that they ran.
*
* Example:
select * from udf_job_FailedTAB()
*      
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as T-SQL UDF of Week Newsletter Vol 2 #32 07/27/04 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
***************************************************************/
AS RETURN 

SELECT  sj.* 
      , sc.name [Category_Name]
	FROM msdb.dbo.sysjobs sj
       inner join msdb.dbo.sysjobservers sjs         
          on sj.job_id = sjs.Job_id
       left outer join msdb.dbo.syscategories sc
          on sj.category_id = sc.category_id
	WHERE sjs.last_run_outcome = 0

GO
 
GRANT SELECT on dbo.udf_Job_FailedTAB to PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Let's test out the function:

select name
      , Category_Name
      , originating_servername
    from udf_job_FailedTAB()
GO

(Results)
name         Category_Name originating_servername  
------------ ------------- ---------------------- 
PurTran1     Purchasing    tstsvr           

I only have one job that's failing and there are plenty of other columns in the output but you get the idea.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick