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;