/*
Author: Theo Ekelmans
Original link: http://www.sqlservercentral.com/articles/Agent+jobs/127346/
*/
--****************************************************************************************
-- This script returns a (graphical) timeline for all SQL jobs using google graph
--****************************************************************************************
-- Version: 1.1
-- Author: Theo Ekelmans
-- Email: [email protected]
-- Date: 2015-06-24
-- Version: 1.2
-- Author: Theo Ekelmans
-- Email: [email protected]
-- Date: 2015-07-28
-- Change: Updated using feedback from the SqlServerCentralCommunity
--****************************************************************************************
set nocount on
declare @DT datetime
declare @StartDT datetime
declare @EndDT datetime
declare @MinRuntimeInSec int
declare @SendMail int
declare @ReturnRecocordset int
declare @Emailprofilename varchar(50)
declare @EmailRecipients varchar(50)
--***************************************************************************************
-- Set variables
--***************************************************************************************
set @StartDT = getdate() - 1
set @EndDT = getdate()
set @MinRuntimeInSec = 60 --Ignore jobs with runtime smaller then this
set @ReturnRecocordset = 0
set @SendMail = 1
set @Emailprofilename = ''
set @EmailRecipients = ''
--***************************************************************************************
-- Pre-run cleanup (just in case)
--***************************************************************************************
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..##GoogleGraph') IS NOT NULL DROP TABLE ##GoogleGraph;
--***************************************************************************************
-- Create a table for HTML assembly
--***************************************************************************************
create table ##GoogleGraph ([ID] [int] IDENTITY(1,1) NOT NULL,
[HTML] [varchar](8000) NULL)
--***************************************************************************************
-- Create the Job Runtime information table
--***************************************************************************************
select job.name as JobName
,cat.name as CatName
,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
into #JobRuntime
FROM msdb.dbo.sysjobs job
left JOIN msdb.dbo.sysjobhistory his
ON his.job_id = job.job_id
INNER JOIN msdb.dbo.syscategories cat
ON job.category_id = cat.category_id
where CONVERT(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
and step_id = 0 -- step_id = 0 is the entire job, step_id > 0 is actual step number
and ((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 > @MinRuntimeInSec -- Ignore trivial runtimes
order by SDT
if not exists (select 1 from #JobRuntime)
goto NothingToDo
--***************************************************************************************
-- Format for google graph - Header
-- (Split into multiple inserts because the default text result setting is 256 chars)
--***************************************************************************************
insert into ##GoogleGraph (HTML)
select '
'
insert into ##GoogleGraph (HTML)
select '
'
+''
+'Job timeline on: '+@@servername
+' from '+convert(varchar(20), @StartDT, 120)
+' until '+convert(varchar(20), @EndDT, 120)
+case when @MinRuntimeInSec = 0 then '' else ' (hiding jobs with runtime < '+cast(@MinRuntimeInSec as varchar(10))+' seconds)' end
+'
'
--***************************************************************************************
-- Output HTML page - copy output & paste to a .HTML file and open with google chrome
--***************************************************************************************
if @ReturnRecocordset = 1
select html from ##GoogleGraph order by ID
--***************************************************************************************
-- Send Email -
--***************************************************************************************
if @SendMail = 1
execute msdb.dbo.sp_send_dbmail
@profile_name = @Emailprofilename
,@recipients = @EmailRecipients
,@subject = 'JobTimeline'
,@body = 'See attachment for JobTimeline, open with Google Chrome!'
,@body_format = 'HTML' -- or TEXT
,@importance = 'Normal' --Low Normal High
,@sensitivity = 'Normal' --Normal Personal Private Confidential
,@execute_query_database = 'master'
,@query_result_header = 1
,@query = 'set nocount on; SELECT HTML FROM ##GoogleGraph'
,@query_result_no_padding = 1 -- prevent SQL adding padding spaces in the result
--,@query_no_truncate= 1 -- mutually exclusive with @query_result_no_padding
,@attach_query_result_as_file = 1
,@query_attachment_filename= 'JobTimeline.HTML'
goto Cleanup
--***************************************************************************************
-- Just in case....
--***************************************************************************************
NothingToDo:
print 'No job runtime info found....'
--***************************************************************************************
-- Cleanup
--***************************************************************************************
Cleanup:
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..##GoogleGraph') IS NOT NULL DROP TABLE ##GoogleGraph;