InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 12:01:17 GMT20http://www.sqlservercentral.com/Forums/Topic661104-1461-1.aspxI started down that line but found that didn't give me everything. I now have stored procedures for 1) Identifying if a backup job exists on a database - we have a convention that every database has an associated backup device so the logic should be a job is created to perform a backup.2) Identifying failed jobs and also jobs that are either disabled or have never been runThe one thing I haven't checked is that a backup has been run on a database "today" because I am assuming that if a job is defined then an appropriate schedule is set for the job (it's a small community creating backup jobs so fairly safe here!)So for the 1st: [code]select [name] from sys.sysdatabases where [name] not in ( select database_name from msdb..backupset ) and [name] not in ('tempdb','model')[/code]and the 2nd[code]select null , j.name , '' , null , null , case when j.enabled != 1 then 'Disabled' else 'Never Run' end as "Run Status" , null , null , null , @@servername , j.job_id from msdb..sysjobs j where ( j.job_id not in ( SELECT h.job_id FROM [msdb].[dbo].[sysjobhistory] h where j.job_id = h.job_id ) ) or (j.enabled != 1) union select cast(h2.instance_id as int) , j.name , h2.step_name , h2.step_id , h2.[message] , case when h2.run_status = 0 then 'Failed' when h2.run_status = 3 then 'Cancelled' else 'Not listed' end as "Run Status" , h2.run_date , h2.run_time , h2.operator_id_emailed as "Operator Emailed" , h2.server , h2.job_id from msdb..sysjobhistory h2 join msdb..sysjobs j on j.job_id = h2.job_id where h2.instance_id in ( SELECT max(h.instance_id) FROM [msdb].[dbo].[sysjobhistory] h group by h.[job_id] ) and h2.run_status in (0,2) order by 7,8, j.name, 3[/code]I doubt it's the neatest or most efficient code but it's pretty simple and ties in with a string of other monitoring routines we use to summarise the state of play first thing in the morning quickly & easily.Hope that might help other people a little.Tue, 17 Mar 2009 07:01:20 GMTBalancehttp://www.sqlservercentral.com/Forums/Topic661104-1461-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/T-SQL/65937/">Find failed SQL Jobs</A>[/B]Fri, 20 Feb 2009 01:19:43 GMTfree_mascot