SQL Job Timeline (Graphical)

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4498

    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 🙂

  • Jonathan AC Roberts

    SSCoach

    Points: 17337

    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)"

  • Adam Seniuk

    SSCrazy

    Points: 2281

    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


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • Derek Wharton

    Old Hand

    Points: 355

    I get the error in SQL 2005, 08 and 08R2

  • Lee Linares

    SSCrazy

    Points: 2693

    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

  • Adam Seniuk

    SSCrazy

    Points: 2281

    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.


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • Adam Seniuk

    SSCrazy

    Points: 2281

    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.


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4498

    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 Seniuk: you can set the resolution here (down to 1 minute) : set @resolution = 1 -- Enter the Resolution in minutes

    Theo 🙂

  • Phil-94129

    Mr or Mrs. 500

    Points: 532

    What format is the "Excel" worksheet in - I downloaded a .zip file, with a series of .xml files - no xls or .xlsx files.

  • Lee Linares

    SSCrazy

    Points: 2693

    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

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4498

    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 🙂

  • Jonathan AC Roberts

    SSCoach

    Points: 17337

    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.

  • The Wizard Of Oz

    SSC Eights!

    Points: 845

    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;

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4498

    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

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    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 40 total)

You must be logged in to reply to this topic. Login to reply