This script is amazing! Thank you Theo!
I found it didn't run on SQL 2000,
so I half-ported it and added a few performance/aesthetic tweaks
and an option to only show times where 2 or more jobs were running.
Thanks again!
--************************
-- This script returns a (graphical) timeline for all SQL jobs
--************************
--Pre Cleanup
IF OBJECT_ID('tempdb..#Timeline') IS NOT NULL DROP TABLE #Timeline;
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..#Pivot') IS NOT NULL DROP TABLE #Pivot;
IF OBJECT_ID('tempdb..#JobNames') IS NOT NULL DROP TABLE #JobNames;
GO
SET NOCOUNT ON
DECLARE @Minutes TABLE (DT DATETIME)
DECLARE @Minutes2 TABLE (col2set VARCHAR(250) NOT NULL PRIMARY KEY)
DECLARE
@dt DATETIME, @StartDT DATETIME, @EndDT DATETIME,
@resolution INT, @RemoveNonactiveJobs INT, @IgnoreDisabledJobs INT, @ShowJobCollisionsOnly INT
SET @StartDT = getdate() - 8 --how far back to look, in days
SET @EndDT = getdate()
SET @resolution = 15 -- Enter the Resolution in minutes
SET @RemoveNonActiveJobs = 1
SET @IgnoreDisabledJobs = 1
SET @ShowJobCollisionsOnly = 1 --only brings back times when 2 or more jobs were running (set to -1 if you want to show the no-jobs-running timeslots)
--***************************
-- Make a Jobname table
--***************************
SELECT DISTINCT
replace(NAME,',', '') AS JobName --Replace commas (they mess up @cols variable used lower in the code)
INTO #JobNames
FROM msdb.dbo.sysjobs
WHERE enabled = @IgnoreDisabledJobs
--***************************
-- Generate a Datetime table between StartDT and EndDT with x minute Resolution
--***************************
SET @dt = @StartDT
WHILE @dt <= @EndDT
BEGIN
INSERT INTO @Minutes (DT) VALUES (@DT)
SET @dt = dateadd(mi, @resolution, @dt)
END
--***************************
-- Create a timeline table by crossjoining the Datetime and Jobnames tables
--***************************
SELECT DT,
JobName,
0 AS Active
INTO #Timeline
FROM @Minutes
CROSS JOIN #JobNames
--***************************
-- Create the Job Runtime information table
--***************************
SELECT
replace(NAME,',', '') AS NAME, --Replace commas (they mess up @cols variable used lower in the code)
CAST(
SUBSTRING(CAST(run_date as varchar(8)),1,4)+'-'+
SUBSTRING(CAST(run_date as varchar(8)),5,2)+'-'+
SUBSTRING(CAST(run_date as varchar(8)),7,2)+' '+
SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),1,2)+':'+
SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),3,2)+':'+
SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),5,2)
as datetime)'SDT',
CAST(
SUBSTRING(CAST(run_date as varchar(8)),1,4)+'-'+
SUBSTRING(CAST(run_date as varchar(8)),5,2)+'-'+
SUBSTRING(CAST(run_date as varchar(8)),7,2)+' '+
SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),1,2)+':'+
SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),3,2)+':'+
SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),5,2)
as datetime)+
CAST(
'1900-01-'+
SUBSTRING(RIGHT('00'+CAST(run_duration/10000/24 +1 as varchar(4)),2),1,2)+' '+
SUBSTRING(RIGHT('00'+CAST(run_duration/10000%24 as varchar(4)),2),1,2)+':'+
SUBSTRING(RIGHT('000000'+CAST(run_duration as varchar(6)),6),3,2)+':'+
SUBSTRING(RIGHT('000000'+CAST(run_duration as varchar(6)),6),5,2)
as datetime)
'EDT'
INTO #JobRuntime
FROM msdb.dbo.sysjobs job
INNER JOIN msdb.dbo.sysjobhistory his ON his.job_id = job.job_id
AND step_id = 0 --only gives back Job history, not Step history
AND job.NAME NOT IN ('Database Mirroring Monitor Job','<dbname>')
AND
CAST(
SUBSTRING(CAST(run_date as varchar(8)),1,4)+'-'+
SUBSTRING(CAST(run_date as varchar(8)),5,2)+'-'+
SUBSTRING(CAST(run_date as varchar(8)),7,2)+' '+
SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),1,2)+':'+
SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),3,2)+':'+
SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),5,2)
as datetime) BETWEEN @StartDT AND @EndDT
ORDER BY SDT
--***************************
-- Update the Timeline based on the the Job Runtime information table
--***************************
UPDATE #Timeline
SET Active = 1
FROM #Timeline
INNER JOIN #JobRuntime ON JobName = NAME
AND(SDT BETWEEN dt AND dateadd(mi, @resolution - 1, DT) -- Start point (added for Resolution support)
OR EDT BETWEEN dt AND dateadd(mi, @resolution, DT) -- End point (added for Resolution support)
OR DT BETWEEN SDT AND EDT
)
--***************************
-- Delete all jobs from the Timeline that that had no activity
--***************************
IF @RemoveNonactiveJobs = 1
DELETE
FROM #Timeline
WHERE JobName NOT IN(
SELECT Jobname
FROM #Timeline
WHERE active = 1
GROUP BY Jobname
)
--***************************
-- SQL 2000: Show Job Run Details
--***************************
IF @@VERSION LIKE '%SQL Server 2000%'
SELECT DISTINCT
counts.DT,
counts.TotalRunningJobs,
t.JobName
FROM #Timeline t
INNER JOIN
(SELECT COUNT(DISTINCT JobName)'TotalRunningJobs',DT
FROM #Timeline
WHERE active=1
GROUP BY DT
HAVING COUNT(DISTINCT JobName) > @ShowJobCollisionsOnly
)counts ON t.DT=counts.DT AND t.active=1
ORDER BY 1 DESC,2 DESC,3
--***************************
-- SQL 2005 and later: Pivot the Timeline table
--***************************
IF @@VERSION NOT LIKE '%SQL Server 2000%'
BEGIN
CREATE TABLE #Pivot (
col1 VARCHAR(250) NULL,
col2 VARCHAR(250) NULL,
col3 INT NULL
)
-- col1 = row, col2 = column, col3 = data
INSERT INTO #Pivot
SELECT DISTINCT
convert(VARCHAR(250), DT, 120) DT,
JobName,
Active
FROM #Timeline
-- Make a table with all unique col2 values
INSERT INTO @Minutes2
SELECT DISTINCT col2
FROM #Pivot
--SELECT * FROM #Pivot --debug check
DECLARE @cols AS NVARCHAR(MAX);SET @cols = '' --in SQL 2000, change MAX to 4000
DECLARE @sql AS NVARCHAR(MAX);SET @sql = '' --in SQL 2000, change MAX to 4000
SELECT @cols = @cols +
CASE
WHEN @cols = '' THEN ''
ELSE ',' + CHAR(10) --add comma and a newline
END
+ quotename(col2set)
FROM @Minutes2
GROUP BY col2set
ORDER BY col2set
--PRINT @cols --debug option
-- Build the pivot statement as a dynamic sql statement
SET @sql = N'
SELECT
col1 as Date,
' + REPLACE(@cols,',','+') + N'as TotalRunningJobs,' +
@cols + N'
FROM
(SELECT *
FROM #Pivot
)AS D
PIVOT
(MAX(col3)
FOR col2 IN(' +@cols + N')
)AS P
WHERE ' +REPLACE(@cols,',','+') + ' > '+CAST(@ShowJobCollisionsOnly as nvarchar(10))+'
ORDER BY 1'
--PRINT @sql --debug option
--***************************
-- Output the Timeline table
--***************************
EXEC sp_executesql @sql
END --of the SQL 2005+ IF statement
--Post Cleanup
IF OBJECT_ID('tempdb..#Timeline') IS NOT NULL DROP TABLE #Timeline;
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..#Pivot') IS NOT NULL DROP TABLE #Pivot;
IF OBJECT_ID('tempdb..#JobNames') IS NOT NULL DROP TABLE #JobNames;