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