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