InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 15:48:01 GMT20http://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxHi Lisa!If you really want to see [b]all[/b] the time-slots, not just the "interesting ones" (where 1 or more jobs are running),then grab the now-fixed script from above and [b]SET @ShowJobCollisionsOnly = -1[/b]I took out the no-jobs-running timeslots to cut down on the data the script returns, to make it easier for people to scan the "important" bits, but I guess should have left the option to display them in there for people who wanted it :-)Fri, 10 May 2013 03:09:51 GMTThe Wizard Of Ozhttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxThis script is great!!! It's something I've wanted but even beginning it was way over my head. I assume you are using conditional formatting on the cell color in Excel? Brilliant!Instead of the previous 24 hours, do you have anything that looks at what's sheduled and coming up? Thanks!--@ The Wiz, when I run your script, it does not give each time increment, only the records where the job ran. So when I put it in Excel, I don't get the graph I get when the orig. script is pasted in with (all 0s and 1s).Thu, 09 May 2013 10:11:24 GMTlisa.randleshttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxHi Vinay,the trick is to right-click on the results and select "Copy with Headers".That copies the column names too.Tue, 12 Feb 2013 02:02:46 GMTThe Wizard Of Ozhttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxTheo, 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 VinayMon, 11 Feb 2013 22:54:21 GMTDanny Oceanhttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxAhhh...This is why i like SqlCentral so much, its truly is a place where ideas can be shared :-)Thanks for posting the tweaked version!TheoThu, 31 Jan 2013 10:16:23 GMTTheo Ekelmanshttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxThis 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 tweaksand an option to only show times where 2 or more jobs were running.Thanks again![code="sql"]--************************-- This script returns a (graphical) timeline for all SQL jobs--************************--Pre CleanupIF 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;GOSET NOCOUNT ONDECLARE @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 INTSET @StartDT = getdate() - 8 --how far back to look, in daysSET @EndDT = getdate()SET @Resolution = 15 -- Enter the Resolution in minutesSET @RemoveNonActiveJobs = 1SET @IgnoreDisabledJobs = 1SET @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 DISTINCTreplace(NAME,',', '') AS JobName --Replace commas (they mess up @cols variable used lower in the code)INTO #JobNamesFROM msdb.dbo.sysjobsWHERE enabled = @IgnoreDisabledJobs--***************************-- Generate a Datetime table between StartDT and EndDT with x minute Resolution--***************************SET @DT = @StartDTWHILE @DT <= @EndDTBEGIN 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 ActiveINTO #TimelineFROM @MinutesCROSS 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 #JobRuntimeFROM msdb.dbo.sysjobs jobINNER JOIN msdb.dbo.sysjobhistory his ON his.job_id = job.job_idAND step_id = 0 --only gives back Job history, not Step historyAND 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 @EndDTORDER BY SDT--***************************-- Update the Timeline based on the the Job Runtime information table--***************************UPDATE #TimelineSET Active = 1FROM #TimelineINNER 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%'BEGINCREATE TABLE #Pivot ( col1 VARCHAR(250) NULL, col2 VARCHAR(250) NULL, col3 INT NULL )-- col1 = row, col2 = column, col3 = dataINSERT INTO #PivotSELECT DISTINCT convert(VARCHAR(250), DT, 120) DT, JobName, ActiveFROM #Timeline-- Make a table with all unique col2 valuesINSERT INTO @Minutes2SELECT DISTINCT col2FROM #Pivot--SELECT * FROM #Pivot --debug checkDECLARE @cols AS NVARCHAR(MAX);SET @cols = '' --in SQL 2000, change MAX to 4000DECLARE @sql AS NVARCHAR(MAX);SET @sql = '' --in SQL 2000, change MAX to 4000SELECT @cols = @cols +CASE WHEN @cols = '' THEN '' ELSE ',' + CHAR(10) --add comma and a newlineEND+ quotename(col2set)FROM @Minutes2GROUP BY col2setORDER BY col2set--PRINT @cols --debug option-- Build the pivot statement as a dynamic sql statementSET @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 @sqlEND --of the SQL 2005+ IF statement--Post CleanupIF 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;[/code]Thu, 31 Jan 2013 09:03:08 GMTThe Wizard Of Ozhttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxThank 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.Tue, 29 Jan 2013 09:49:20 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxSome 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 :)Tue, 29 Jan 2013 09:34:55 GMTTheo Ekelmanshttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxI 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. LeeTue, 29 Jan 2013 09:34:48 GMTLee Linareshttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxWhat format is the "Excel" worksheet in - I downloaded a .zip file, with a series of .xml files - no xls or .xlsx files.Tue, 29 Jan 2013 09:16:10 GMTPhil-94129http://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxHi 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 Seniuk: you can set the resolution here (down to 1 minute) : set @Resolution = 1 -- Enter the Resolution in minutesTheo :)Tue, 29 Jan 2013 08:45:32 GMTTheo Ekelmanshttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxMy oldest version 2005 9.0.1399 gives me this errorMsg 1001, Level 15, State 1, Line 77Line 77: Length or precision specification 0 is invalid.Tue, 29 Jan 2013 07:44:51 GMTAdam Seniukhttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxCommand(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.Tue, 29 Jan 2013 07:40:45 GMTAdam Seniukhttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxThanks 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.LeeTue, 29 Jan 2013 07:32:31 GMTLee Linareshttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxI get the error in SQL 2005, 08 and 08R2Tue, 29 Jan 2013 07:14:45 GMTDerek Whartonhttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxThis 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. ThanksTue, 29 Jan 2013 07:10:12 GMTAdam Seniukhttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxJust tried running it on SQL Server 2005 and got this error:Msg 1001, Level 15, State 1, Line 77Line 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 DurationIt doesn't like "time(0)"Tue, 29 Jan 2013 04:52:46 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic1412750-596-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Job/96230/">SQL Job Timeline (Graphical)</A>[/B]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 DurationAlternate download location for people having issues with Internet explorer downloading: https://dl.dropbox.com/u/43939704/MS_SQL_JOB_timeline.xlsx Theo :)Mon, 28 Jan 2013 22:10:48 GMTTheo Ekelmans