set nocount on

declare @Minutes table (DT datetime)

declare @JobNames table (JobName varchar(255))

declare @dt datetime

declare @StartDT datetime

declare @EndDT datetime

declare @resolution int

declare @RemoveNonactiveJobs int

declare @IgnoreDisabledJobs int

--***************************************************************************************

-- Set variables

--***************************************************************************************

set @StartDT = getdate() - 1

set @EndDT = getdate()

set @resolution = 1 -- Enter the Resolution in minutes

set @RemoveNonactiveJobs = 1

set @IgnoreDisabledJobs = 1

--***************************************************************************************

-- Pre-run cleanup (just in case)

--***************************************************************************************

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..#tblTLine') IS NOT NULL DROP TABLE #tblTLine;

IF OBJECT_ID('tempdb..#tblTasks') IS NOT NULL DROP TABLE #tblTasks;

--***************************************************************************************

-- Make a Jobname table

--***************************************************************************************

insert into @JobNames (JobName)

selectreplace(name, ' ', '.') --Replace spaces (they are invalid in XML based pivot lower in the code)

frommsdb.dbo.sysjobs

whereenabled = @IgnoreDisabledJobs

--WHERE NAME = '<dbname>'

--WHERE NAME like '%<partial dbname>%'

--***************************************************************************************

-- Genereate 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

--***************************************************************************************

selectreplace(name, ' ', '.') as name --Replace spaces (they are invalid in XML based pivot lower in the code)

--,step_id

--,step_name

,CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120) as SDT

,dateadd(s,

((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 ,

CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120)

) as EDT

--,CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS Duration

,((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 DurationSeconds

into#JobRuntime

FROMmsdb.dbo.sysjobs job

left JOIN msdb.dbo.sysjobhistory his

ON his.job_id = job.job_id

whereCONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120) between @StartDT and @EndDT

andjob.name not in ('Database Mirroring Monitor Job', '<dbname>')

andstep_id = 0 -- step_id = 0 is the entrite job, step_id > 0 is actual step number

--and((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 > 1 -- Ignore trivial runtimes

order by SDT

--***************************************************************************************

-- Update the Timeline based on the the Job Runtime information table

--***************************************************************************************

update#Timeline

setActive = 1

from#Timeline inner join #JobRuntime

onJobName = 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

whereJobName in(selectJobname

from#Timeline

group by Jobname

havingsum(active) = 0 )

--***************************************************************************************

-- Build and Pivot the Timeline table

--***************************************************************************************

create table #Pivot (DT varchar(250) null, Name varchar(250) null, Active int null)

-- col1 = row, col2 = column, col3 = data

insertinto #Pivot

selectconvert(varchar(250), DT, 120), JobName, Active

from#Timeline

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DECLARE @SQL nvarchar(4000)

DECLARE @TaskName nvarchar(100)

SET NOCOUNT ON

CREATE TABLE #tblTLine (

[DT] varchar(200)

)

CREATE TABLE #tblTasks (

[Tasks] varchar(200)

)

INSERT INTO #tblTasks (

[Tasks]

)

select DISTINCT

Name

from #Pivot

INSERT INTO #tblTLine (

[DT]

)

select DISTINCT

[DT]

from #Pivot

ORDER BY DT

--WHERE Active = 1

-- Build Table

DECLARE cur CURSOR FOR

select DISTINCT

[Tasks]

from #tblTasks

OPEN cur

FETCH NEXT FROM cur INTO @TaskName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'ALTER TABLE #tblTLine ADD [' + @TaskName + '] nchar(1) NULL'

EXEC (@SQL)

SET @SQL = ''

SET @SQL = 'UPDATE #tblTLine SET [' + @TaskName + '] = ''0'''

EXEC (@SQL)

FETCH NEXT FROM cur INTO @TaskName

END

CLOSE cur

DEALLOCATE cur

-- Update Table

DECLARE @SQLUpdate nvarchar(4000)

DECLARE @Time nvarchar(100)

DECLARE @Name nvarchar(100)

DECLARE @Active nchar(1)

DECLARE curUpdate CURSOR FOR

SELECT

[DT],

[Name],

[Active]

FROM #Pivot

WHERE Active = 1

OPEN curUpdate

FETCH NEXT FROM curUpdate INTO @Time, @Name, @Active

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLUpdate = 'UPDATE #tblTLine SET [' + @Name + '] = ''1'' WHERE [DT] = ''' + @Time + ''''

EXEC (@SQLUpdate)

FETCH NEXT FROM curUpdate INTO @Time, @Name, @Active

END

CLOSE curUpdate

DEALLOCATE curUpdate

SET NOCOUNT OFF

--***************************************************************************************

-- Output the Timeline table

--***************************************************************************************

SELECT * FROM #tblTLine

--***************************************************************************************

-- 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..#tblTLine') IS NOT NULL DROP TABLE #tblTLine;

IF OBJECT_ID('tempdb..#tblTasks') IS NOT NULL DROP TABLE #tblTasks;