All jobs that ran during a given time frame.

Leave a comment

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
GO

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

SELECT sysjobs.name 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

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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,569 other followers

Follow me on Twitter

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