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@Nowdatetime,--Day to analyse
@Datechar(8),--yyyymmdd format of @Now
@JobNamevarchar(255),--stores name of Jobs when creating results table
@SQLvarchar(512),--used for dynamic SQL
@TimeGranualityint,--the granuality of time. This value should be expressed in seconds
@JobRunTimevarchar(10),--Time job ran (extracted from sysJobHistory)
@JobDurationvarchar(10),--Duration of job execution (extracted from sysJobHistory). Format of this is hhmmss
@JobRunTime_datetimedatetime--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 
