All jobs that ran during a given time frame.

,

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

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

5 (1)

Share

Share

Rate

5 (1)