This article is the second in a series of four posts about a process to search active SQL Agent jobs. The first three articles describe some utility functions, and the fourth post will pull them all together.

1. Find which job steps are reachable
2. (This post) Determine if a job is scheduled
3. Determine if a job has executed recently
4. Search only active jobs

Finding scheduled jobs

The function below is an inline table-valued function that will return a value indicating if a job is scheduled. The logic is considerably simple than what was needed in the previous post where we determined which job steps are reachable.

Considerations

Parameters: The function takes no arguments.

Output: The function returns one row per job. The columns are:

• job_id (the uniqueidentifier of the job as found in msdb.dbo.sysjobs)
• job_name (the name of the job as found in msdb.dbo.sysjobs)
• is_scheduled (a boolean that is true if the job is scheduled and false if not)

Example usage:

select job_id, job_name, is_scheduled
from dbo.fn_IsJobScheduled();

Discussion: We are defining a job as scheduled if all of the following are true:

• The job has one or more schedules associated to it
• At least one of the schedules is enabled
• The enabled schedule must have a recurrence type of one-time, daily, weekly or monthly
• The end date on the schedule must be in the future

The Code

if exists (select * from sys.objects o join sys.schemas s on o.schema_id = s.schema_id where s.name = 'dbo' and o.name = 'fn_IsJobScheduled' and o.type = 'IF')
	drop function dbo.fn_IsJobScheduled;
go
create function dbo.fn_IsJobScheduled ()
returns table
as
	return
		select j.job_id,
			j.name job_name,
			cast(case when exists
			(
				select *
				from msdb.dbo.sysjobschedules js 
				join msdb.dbo.sysschedules s on s.schedule_id = js.schedule_id
				where js.job_id = j.job_id
				and s.enabled = 1
				and s.freq_type in (1, 4, 8, 16, 32)
				and msdb.dbo.agent_datetime(s.active_end_date, s.active_end_time) > getdate()
			) then 1 else 0 end as bit) is_scheduled
		from msdb.dbo.sysjobs j;
go