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