This article is the fourth and final 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. Determine if a job is scheduled
3. Determine if a job has executed recently
4. (This post) Search only active jobs

Searching only active jobs

My motivation for wanting to search only active jobs came several months ago when I needed to search every job across several dozen instances for different strings that I needed to change. The updates that I needed to make were rather laborious and needed to be done manually, and we have a number of disabled jobs and other situations where it would be a waste of time to make these changes, at least in the short-term.

Therefore, I wanted to filter out jobs and/or job steps that don’t typically get executed. (In my particular case, it wasn’t such a big deal if I missed something because the changes would merely cause the job step to fail, which would in turn send an alert, and I could fix the problem in those few cases. I think this wound up happening twice, but that in turn led me to refine these queries to address those situations.)

So I established the following criteria to eliminate results.

• The job needs to be enabled. However, it is entirely possible for a disabled to job to run. For example, it can be manually started, or it may be started by a different job or an external process, so additional criteria are needed.

• The job needs to be scheduled, although for the same reasons, this won’t catch everything since jobs can be triggered via other mechanisms.

• To catch jobs that are either disabled or unscheduled, we also want to check if the job has run “recently.” I figure that if for any reason it has executed in the past week or month or whatever, it is something that I’m interested in looking at.

Considerations

The exact nature of the search can vary quite a bit depending how the jobs are set up and what we want to find. Usually, I will search the job name or the step for certain text, or (most commonly of all), I will search the command text, as is done in the sample query below.

Input values: The sample query uses the following variables (at the top of the script) to control the search. These will vary according to your specific search needs.

• @searchPattern (nvarchar(max)): A pattern indicating the search criteria.
• @activeCutoffDate (datetime): A value indicating the threshold for how recent a job must have executed to be considered “recent.”

Output: The sample query returns these columns, but you may want to vary the output to your needs.

• job_name (the name of the job as found in msdb.dbo.sysjobs)
• step_name (the name of the job step as found in msdb.dbo.sysjobsteps)
• command (the command behind the job step)

Sample search code

declare @searchPattern nvarchar(max) = '%search%';
declare @activeCutoffDate datetime = dateadd(day, -7, getdate());

select sr.job_name, sr.step_name, js.command
from CI_Admin.dbo.fn_AreJobStepsReachable(null) sr
inner join CI_Admin.dbo.fn_IsJobScheduled() sch on sch.job_id = sr.job_id
inner join CI_Admin.dbo.fn_HasJobRunRecently(@activeCutoffDate) rr on rr.job_id = sr.job_id
inner join msdb.dbo.sysjobsteps js on js.job_id = sr.job_id and js.step_id = sr.step_id
where sr.is_reachable = 1
and (sch.is_scheduled = 1 or rr.has_run_recently = 1)
and js.command like @searchPattern;

This article is the third 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. Determine if a job is scheduled
3. (This post) Determine if a job has executed recently
4. Search only active jobs

Finding jobs that have executed recently

The function below is an inline table-valued function that will return a value indicating if a job has been executed recently, with “recently” being defined by an input parameter. The logic is considerably simple than what was needed in the first post where we determined which job steps are reachable.

Considerations

Parameters: The function takes one argument that is a threshold date for defining “recent.”

• @cutoff_date (datetime): A value indicating the threshold for how recent a job must have executed to be considered “recent.”

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)
• last_run_date (a datetime indicating the last time that the job executed, or NULL if the last run time cannot be determined, which may indicate that the job has never executed or that since the last execution, job history has been purged)
• has_run_recently (a boolean that is true if the job has executed “recently” and false if not)

Example usage:

select job_id, name, last_run_date, has_run_recently from fn_HasJobRunRecently(dateadd(day, -5, getdate()));

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_HasJobRunRecently' and o.type = 'IF')
	drop function dbo.fn_HasJobRunRecently;
go
create function dbo.fn_HasJobRunRecently
(
	@cutoff_date datetime
)
returns table
as
	return
		select j.job_id, j.name, jh.last_run_date,
			cast(case when jh.last_run_date >= @cutoff_date then 1 else 0 end as bit) has_run_recently
		from msdb.dbo.sysjobs j
		left join
		(
			select jh.job_id,
				cast(
					cast(jh.run_date / 10000 as nvarchar(9)) + N'-' + 
					cast((jh.run_date % 10000) / 100 as nvarchar(9)) + N'-' + 
					cast(jh.run_date % 100 as nvarchar(9)) + N' ' +
					cast(jh.run_time / 10000 as nvarchar(9)) + N':' + 
					cast((jh.run_time % 10000) / 100 as nvarchar(9)) + N':' + 
					cast(jh.run_time % 100 as nvarchar(9))
				as datetime) last_run_date,
				row_number() over (partition by jh.job_id order by jh.run_date desc, jh.run_time desc) rn
			from msdb.dbo.sysjobhistory jh
		) jh on j.job_id = jh.job_id and jh.rn = 1
go

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

This article is the first 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. (This post) Find which job steps are reachable
2. Determine if a job is scheduled
3. Determine if a job has executed recently
4. Search only active jobs

Finding reachable job steps

There are a number of reasons that we might create SQL Agent job where there is at least one job step that isn’t reachable. Perhaps most commonly, we want to have some processing that normally doesn’t run (on a scheduled basis) but that occasionally we might kick off manually.

For this article series, I am presuming that when we search active SQL jobs, we want to ignore any job steps that are unreachable. For instance, we might be doing some refactoring and want to find all jobs that reference certain tables or columns, but don’t really care about job or job steps that are never executed.

I have put together a SQL function to help identify these unreachable job steps. Unfortunately, it is a multi-statement table-valued function (I really dislike MSTVFs), but I haven’t yet figured out how to get all of the logic into an inline TVF. (I got the core logic in place, but the issue I haven’t worked past is how to deal with infinite loops within jobs. In the MSTVF version, this gets handled already, but the inline version uses recursion and chokes on jobs that have infinite loops.)

Considerations

Parameters: The function takes a single argument called @job_name. If this value is NULL, the function processes all jobs on the instance, otherwise, it limits itself to the single specified job. It is worth comparing whether it is more efficient to run the function once for each job you are interested in compared to running the function once for all jobs (NULL input parameter) and caching the results, for example into a temporary table.

Performance: This is an MSTVF, and it has the performance issues associated with these types of functions. I find that with a NULL value for the @job_name parameter, the function typically operates in no more than a few seconds on a instance with several hundred jobs, but the run time seems to depend more on job complexity than anything else.

Output: The function returns one row per job step. 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)
• step_id (the step number of the job as found in msdb.dbo.sysjobsteps)
• step_name (the name of the step as found in msdb.dbo.sysjobsteps)
• is_reachable (a boolean that is true if the step is reachable and false if it is not reachable)

Example usage:

select job_id, job_name, step_id, step_name, is_reachable
from dbo.fn_AreJobStepsReachable(null);

select job_id, job_name, step_id, step_name, is_reachable
from dbo.fn_AreJobStepsReachable('Name of important job');

Discussion: This code works by first identifying all of the job steps in a job and then “visiting” the steps beginning at the one identified as the start step. At each step, we are only interested in the steps marked as either “Go to the next step” or “Go to step n” in either the “On Success” or “On Failure” actions. If one of theses steps has already been visited, we skip it, otherwise we add the step to a queue, and then consider the next step in the queue. This continues until we have exhausted all items from the queue. Any job steps that were not visited are considered unreachable.

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_AreJobStepsReachable' and o.type = 'TF')
	drop function dbo.fn_AreJobStepsReachable;
go
create function dbo.fn_AreJobStepsReachable
(
	@job_name sysname
)
returns @resultTable table
(
	job_id uniqueidentifier,
	job_name nvarchar(128),
	step_id int,
	step_name nvarchar(128),
	is_reachable bit
)
as
begin
	declare @jobInfo table
	(
		job_id uniqueidentifier,
		start_step_id int,
		step_id int,
		on_success_action tinyint,
		on_success_step_id int,
		on_fail_action tinyint,
		on_fail_step_id int,
		is_reachable bit
	);

	declare @queue table
	(
		ID int not null identity(1,1) primary key clustered,
		job_id uniqueidentifier,
		step_id int
	);

	declare @next_queue table
	(
		ID int not null identity(1,1) primary key clustered,
		job_id uniqueidentifier,
		step_id int
	);

	if @job_name is null
	begin
		insert @jobInfo (job_id, start_step_id, step_id, on_success_action, on_success_step_id, on_fail_action, on_fail_step_id, is_reachable)
		select
				j.job_id, 
				j.start_step_id, 
				js.step_id, 
				js.on_success_action, 
				js.on_success_step_id, 
				js.on_fail_action, 
				js.on_fail_step_id,
				cast (0 as bit) is_reachable
		from msdb.dbo.sysjobs j
		inner join msdb.dbo.sysjobsteps js on js.job_id = j.job_id
	end
	else
	begin
		insert @jobInfo (job_id, start_step_id, step_id, on_success_action, on_success_step_id, on_fail_action, on_fail_step_id, is_reachable)
		select
				j.job_id, 
				j.start_step_id, 
				js.step_id, 
				js.on_success_action, 
				js.on_success_step_id, 
				js.on_fail_action, 
				js.on_fail_step_id,
				cast (0 as bit) is_reachable
		from msdb.dbo.sysjobs j
		inner join msdb.dbo.sysjobsteps js on js.job_id = j.job_id
		where j.name = @job_name;
	end

	insert @queue (job_id, step_id)
	select distinct job_id, start_step_id
	from @jobInfo;

	while exists (select * from @queue)
	begin
		update j
		set is_reachable = 1
		from @jobInfo j
		inner join @queue q on q.job_id = j.job_id and q.step_id = j.step_id;

		with NextStep as
		(
			select j.job_id,
				case j.on_success_action when 3 then q.step_id + 1
					when 4 then j.on_success_step_id end as next_step_id
			from @jobInfo j
			join @queue q on q.job_id = j.job_id and q.step_id = j.step_id
			where j.on_success_action in (3, 4)

			union all

			select j.job_id,
				case j.on_fail_action when 3 then q.step_id + 1
					when 4 then j.on_fail_step_id end as next_step_id
			from @jobInfo j
			inner join @queue q on q.job_id = j.job_id and q.step_id = j.step_id
			where j.on_fail_action in (3, 4)
		)
		insert @next_queue (job_id, step_id)
		select j.job_id, j.step_id
		from @jobInfo j
		inner join NextStep ns on j.job_id = ns.job_id and j.step_id = ns.next_step_id
		where j.is_reachable = 0;

		delete @queue;

		insert @queue (job_id, step_id)
		select job_id, step_id
		from @next_queue;

		delete @next_queue;
	end

	insert @resultTable (job_id, job_name, step_id, step_name, is_reachable)
	select ji.job_id, j.name job_name, ji.step_id, js.step_name, is_reachable
	from @jobInfo ji
	inner join msdb.dbo.sysjobs j on j.job_id = ji.job_id
	inner join msdb.dbo.sysjobsteps js on ji.job_id = js.job_id and ji.step_id = js.step_id;

	return;
end;
go