January 28, 2013 at 10:10 pm
Comments posted to this topic are about the item <A HREF="/scripts/Job/96230/">SQL Job Timeline (Graphical)</A>
Thanks for the feedback, and yes.... My bad, i left ONE line of code in that is SQL 2012 specific, if you comment out line 77, the code will work in 2005 (i tested it with build 4053).
--,CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS Duration
Alternate download location for people having issues with Internet explorer downloading:
https://dl.dropbox.com/u/43939704/MS_SQL_JOB_timeline.xlsx
Theo 🙂
January 29, 2013 at 4:52 am
Just tried running it on SQL Server 2005 and got this error:
Msg 1001, Level 15, State 1, Line 77
Line 77: Length or precision specification 0 is invalid.
With this line highlighted:
,CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS Duration
It doesn't like "time(0)"
January 29, 2013 at 7:10 am
This ran fine on SQL 2008 and SQL 2008 R2.
This would be a great report to run on systems with a few parameters like date range and increment amount (aka Resolution)
Great job, always like seeing what my jobs are doing.
Thanks
January 29, 2013 at 7:14 am
I get the error in SQL 2005, 08 and 08R2
January 29, 2013 at 7:32 am
Thanks for taking the time and effort to share this script with us. I downloaded the file from your link for the sample Excel file but the zip file that was downloaded did not contain any Excel file. Did I misunderstand something? Thanks again.
Lee
January 29, 2013 at 7:40 am
Command(s) completed successfully. - I ran this on 10.50.4263, 10.50.2500, 10.50.4000 and 10.0.5826 with no issues...
What versions are you running we may have a fix applied in the most upto date version that is allowing it to work.
January 29, 2013 at 7:44 am
My oldest version 2005 9.0.1399 gives me this error
Msg 1001, Level 15, State 1, Line 77
Line 77: Length or precision specification 0 is invalid.
January 29, 2013 at 8:45 am
Hi Guys,
Thanks for the feedback, and yes.... My bad, i left ONE line of code in that is SQL 2012 specific, if you comment out line 77, the code will work in 2005 (i tested it with build 4053).
--,CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS Duration
@lee Linares: The link points to a EXCEL 2010 sheet, not a zip file.
@adam-2 Seniuk: you can set the resolution here (down to 1 minute) : set @resolution = 1 -- Enter the Resolution in minutes
Theo 🙂
January 29, 2013 at 9:16 am
What format is the "Excel" worksheet in - I downloaded a .zip file, with a series of .xml files - no xls or .xlsx files.
January 29, 2013 at 9:34 am
I also had the same problem as Phil-94129 but after communicating with Theo (thanks Theo) and trying several suggestions I found that I only had this download problem using Internet Explorer. I had a team mate try it with FireFox and the download as an Excel file worked perfectly.
Lee
January 29, 2013 at 9:34 am
Some people report errors downloading the EXCEL file with Internet Explorer, please try another browser (chrome of Firefox).
I suspect yahoo hosting does something weird when IE downloads.
Or try this link: https://dl.dropbox.com/u/43939704/MS_SQL_JOB_timeline.xlsx
Theo 🙂
January 29, 2013 at 9:49 am
Thank you, it's an excellent script and combined with the spreadsheet is a great way to check your scheduled jobs as a gantt chart rotated by 90 degrees.
January 31, 2013 at 9:03 am
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;
January 31, 2013 at 10:16 am
Ahhh...
This is why i like SqlCentral so much, its truly is a place where ideas can be shared 🙂
Thanks for posting the tweaked version!
Theo
February 11, 2013 at 10:54 pm
Theo,
It's really good script. i ran it and it works.
but i have one issue. After getting the result for script, i paste data in excel.
In excel it didn't show lob name. Instead of these it, it show 0.
Thanks
Vinay
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply