All jobs that ran during a given time frame.


May 6, 2021 by Kenneth Fisher

Short post today. Simple query that will tell you every job that ran between two datetime stamps. Note: this of course assumes that the data exists. Job history is only kept for so long so depending on your settings the data may already have been deleted. Also if a job is currently running it’s first step it’s not going to show up in the history table so obviously it won’t show up in the results for this query.

USE msdb

DECLARE @StartCheckDateTime DATETIME = '2/1/2021 1:00 AM'
DECLARE @EndCheckDateTime   DATETIME = '2/1/2021 2:59 AM'

SELECT AS JobName, sysjobhistory.step_name AS JobStepName, sysjobhistory.step_id AS JobStepId,
	Vars.StartDateTime, Vars2.EndDateTime,
FROM msdb.dbo.sysjobhistory
JOIN msdb.dbo.sysjobs
	ON sysjobhistory.job_id = sysjobs.job_id
CROSS APPLY (SELECT msdb.dbo.agent_datetime(sysjobhistory.run_date, sysjobhistory.run_time) AS StartDateTime,
				(sysjobhistory.run_duration / 10000 * 3600)/*Hours*/ + 
					((sysjobhistory.run_duration % 10000) / 100 * 60)/*Minutes*/ + 
					(sysjobhistory.run_duration % 100) /*Seconds*/ AS RunDurationSec) AS Vars
CROSS APPLY (SELECT DATEADD(SECOND, Vars.RunDurationSec,Vars.StartDateTime) AS EndDateTime) AS Vars2
WHERE Vars.StartDateTime <= @EndCheckDateTime
  AND Vars2.EndDateTime >= @StartCheckDateTime
  AND sysjobhistory.step_id <> 0

One thought on “All jobs that ran during a given time frame.

  1. […] List all the jobs that ran in a given timeframe. […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: