[Maint] Listing jobs that won't run

We all have them. Those jobs that get disabled, or schedules that get mis-set. This aims to fix that. It does two things
* Checks for "enabled = 0" in the job itself
* Checks sysjobschedules to make sure it's going to run

Hope it helps. I've also added code to only look for jobs that have changed in the past two weeks. Also, set up a job (below) that will email you every morning. This isn't really needed if you've got triggers on things, but I haven't implemented that yet.

I believe the SP works on 2000 and 2005, but the job is a 2005 job - I just scripted it out.


--create a database to hold these kinds of objects, and put it on every server you care about
/****** Object: StoredProcedure [dbo].[Monitor_JobChecker] Script Date: 01/29/2008 09:36:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************************

Name: Monitor_JobChecker

Creator: Michael Bourgon

Purpose: Will check and email jobs that have inadvertently been set to not run.
If the enabled is 0, then it's not set as "Runondemand" or "Decommissioned".
If the enabled is 1, then it does not have an active schedule. If it is
part of a SQL Sentry chain, put "event chain" in the description.

Dependencies:

History: 1.00 MDB 20070628 Looks good.
1.1 MDB 20080129 Adding code to only check the past month.


***********************************************************/

CREATE proc [dbo].[Monitor_JobChecker]
as
set nocount on
--Just a cursory check for jobs that aren't enabled
select name,
convert(smalldatetime, date_modified) as date_modified,
enabled,
description
from msdb.dbo.sysjobs
where enabled = 0
and category_id not in
(
select category_id
from msdb.dbo.syscategories
where name in ('Decommissioned', 'RunOnDemand', 'TemporarilyDisabled')
)
--mdb 20080129
and convert(smalldatetime, date_modified) > GETDATE()-14
union all
--A more thorough check that looks at the job schedules
select name,
convert(smalldatetime, date_modified) as date_modified,
enabled,
description
from msdb.dbo.sysjobs
where job_id in
(
select job_id
from msdb.dbo.sysjobschedules sysjobschedules
inner join msdb.dbo.sysschedules sysschedules
on sysjobschedules.schedule_id = sysschedules.schedule_id
where job_id not in
(
select job_id
from msdb.dbo.sysjobschedules
where next_run_date > 0
)
and sysschedules.freq_type <64
)
and enabled > 0
and description not like '%event chain%'
and category_id not in
(
select category_id
from msdb.dbo.syscategories
where name in ('Decommissioned', 'RunOnDemand', 'TemporarilyDisabled')
)
--mdb 20080129
and convert(smalldatetime, date_modified) > GETDATE()-14
order by enabled, name

/* -- to change the job status easily, use this:
USE msdb
EXEC sp_update_job @job_name = 'Re-Run Aggs',
@category_name = 'RunOnDemand'
*/
set nocount off



---------and now the job--------
USE [msdb]
GO
/****** Object: Job [Monitor - Jobs that will not run] Script Date: 01/29/2008 09:49:03 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 01/29/2008 09:49:04 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Monitor - Jobs that will not run',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Check system tables for jobs that will not run, either because they are disabled, or because they have no active schedules. Excluded are RunOnDemand and Decommissioned, or those jobs with "event_chain" (remove underscore) in their description, as those aren''t supposed to have regular schedules.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'support', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Run monitor_jobchecker] Script Date: 01/29/2008 09:49:04 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run monitor_jobchecker',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'create table #temp_monitor (name sysname, date_modified smalldatetime, enabled smallint, description varchar(7500))
insert into #temp_monitor exec msdb.dbo.monitor_jobchecker
if (select count(*) from #temp_monitor) > 0

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''IT_Databases'',
@recipients = ''support@dev.null'',
@query = ''exec dbautils.dbo.monitor_jobchecker'' ,
@subject = ''Monitor (yourservernamehere) - Job Checker'',
@body = ''This is a list of jobs on yourservernamehere that will not run. If enabled = 0, then they are not enabled. If
enabled = 1 then they do not have a schedule that is enabled. To fix please use:
USE msdb
EXEC sp_update_job @job_name = ''''Re-Run Aggs'''',
@category_name = ''''RunOnDemand''''
'',
@query_attachment_filename = ''Jobs That Will Not Run on yourservernamehere.txt'',
@query_result_separator = '' '',
@query_result_width = 750,
@attach_query_result_as_file = 1 ;
',
@database_name=N'msdb', --this should be your DBA database, whatever it's called
@output_file_name=N'C:\Logs\Monitor_-_Jobs_that_will_not_run.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monitor Schedule - daily at 9:30',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20070628,
@active_end_date=99991231,
@active_start_time=93000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: