Show when all jobs were running

,

This script returns a result set showing which jobs were running at what point during a given day.

One column is returned for each Job, and one row for each discrete portion of the day (configured by @TimeGranuality). A value of 1 indicates that the job was running at that point in time, 0 otherwise.

Particularly useful for scheduling jobs so that they do not clash with each other. Pasting the results into Excel and applying a bit of conditional formatting gives a nice graphical display.

DECLARE	@Now					datetime,		--Day to analyse
		@Date					char(8),		--yyyymmdd format of @Now
		@JobName				varchar(255),	--stores name of Jobs when creating results table
		@SQL					varchar(512),	--used for dynamic SQL
		@TimeGranuality			int,			--the granuality of time. This value should be expressed in seconds
		@JobRunTime				varchar(10),	--Time job ran (extracted from sysJobHistory)
		@JobDuration			varchar(10),	--Duration of job execution (extracted from sysJobHistory). Format of this is hhmmss
		@JobRunTime_datetime	datetime		--datetime version of @JobRunTime

SELECT	@Now = '2006-09-13',		--The day you wish to view the jobs history for
		@TimeGranuality = 600		--Granuality in seconds

SELECT @Date = REPLACE(CONVERT(char(10), @Now, 120), '-', '')

--Create table to hold results
CREATE TABLE #Results (RowID int identity (1,1), [Time] smalldatetime)

--Declare cursor to loop through all jobs defined on the server
--Each job will be added as a column to #Results
DECLARE Cur CURSOR FOR
SELECT DISTINCT [name]
FROM  dbo.sysJobs 
ORDER BY [name]

OPEN Cur

FETCH NEXT FROM Cur INTO @JobName

WHILE @@FETCH_STATUS = 0
BEGIN
	--Add column to results
	SELECT @SQL = 'ALTER TABLE #Results ADD [' + @JobName + '] bit'
	EXEC(@SQL)
	FETCH NEXT FROM Cur INTO @JobName
END

--Close cursor
CLOSE Cur
DEALLOCATE Cur

--Add rows to #Results for each point in time for 24 hour period
INSERT #Results ([Time])
SELECT CAST(@Date as datetime)

WHILE ((SELECT MAX([Time]) FROM #Results) < DATEADD(day, 1, CAST(@Date as datetime)))
BEGIN
	INSERT #Results ([Time])
	SELECT DATEADD(second, @TimeGranuality, (SELECT MAX([Time]) FROM #Results))
END

--Declare cursor for each job execution
--Update relvent column for when job was executing
DECLARE Cur CURSOR FOR
SELECT B.[name], A.run_time, A.run_duration
FROM dbo.sysJobHistory A
INNER JOIN dbo.sysJobs B on A.job_id = B.job_id
WHERE A.run_date = @Date	--Only jobs started on required date
AND A.step_id = 0			--Job outcome
ORDER BY B.[name]

OPEN Cur

FETCH NEXT FROM Cur INTO @JobName, @JobRunTime, @JobDuration

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @JobRunTime = REPLICATE('0', 6 - LEN(@JobRunTime)) + @JobRunTime
	SELECT @JobRunTime = SUBSTRING(@JobRunTime, 1, 2) + ':' + SUBSTRING(@JobRunTime, 3, 2) + ':' + SUBSTRING(@JobRunTime, 5, 2)
	SELECT @JobRunTime_datetime = CAST(@Date as datetime) + ' ' + @JobRunTime

  	SELECT @SQL = 'UPDATE #Results SET [' + @JobName + '] = 1 WHERE [Time] >= ''' + cast(@JobRunTime_datetime as varchar) + ''' AND [Time] <= ''' + CAST(DATEADD(SECOND, CAST(SUBSTRING(@JobDuration, LEN(@JobDuration) - 1, 2) AS int), DATEADD(MINUTE,  CAST(SUBSTRING(@JobDuration, LEN(@JobDuration) - 3, 2) AS int), DATEADD(HOUR, CAST(SUBSTRING(@JobDuration, LEN(@JobDuration) - 5, 2) AS int), @JobRunTime_datetime ))) as varchar) + ''''
	EXEC(@SQL)

	FETCH NEXT FROM Cur INTO @JobName, @JobRunTime, @JobDuration
END

--Close cursor
CLOSE Cur
DEALLOCATE Cur

--Return results
SELECT *
FROM #Results

--Drop table
DROP TABLE #Results

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)