Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

SQL Job Timeline (Graphical) Expand / Collapse
Author
Message
Posted Friday, June 14, 2013 3:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:37 AM
Points: 479, Visits: 297
I tried getting the job names to pivot somehow in SQL 2000 (as we have a legacy server running it still...), but I gave up after a while too.
The closest I got to the original "pivoted" data was posted a bit further up in this thread: http://www.sqlservercentral.com/Forums/FindPost1414190.aspx.

A google search for SQL 2000 pivot brings up a few contenders for a solution.
Best of luck, and if anyone manages to solve it, please post the code here for others to use!
Post #1463451
Posted Wednesday, June 19, 2013 4:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 17, 2014 9:12 AM
Points: 4, Visits: 44
Hi guys

Thought i would post again as i have managed to get ths to work for SQL Server 2000.

I know its porbably not the tidyest or most slick way of doing it but it works as the original script does. If anyone can edit and provide the code for a more slick example then i am open to suggestion.


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)
select replace(name, ' ', '.') --Replace spaces (they are invalid in XML based pivot lower in the code)
from msdb.dbo.sysjobs
where enabled = @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
--***************************************************************************************
select replace(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
FROM msdb.dbo.sysjobs job
left JOIN msdb.dbo.sysjobhistory his
ON his.job_id = job.job_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 job.name not in ('Database Mirroring Monitor Job', '<dbname>')
and step_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
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 in ( select Jobname
from #Timeline
group by Jobname
having sum(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


insert into #Pivot
select convert(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;
Post #1465052
Posted Wednesday, June 19, 2013 6:14 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 5:29 AM
Points: 28, Visits: 456
Thanks ryanwooster1980

It will be much appreciated by the poor sods that still have to maintain SQL2000 databases.

I for one still have 2 old boxes that were virtualized because the hardware was getting really unreliable.
Post #1465118
Posted Tuesday, June 25, 2013 9:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 17, 2014 9:12 AM
Points: 4, Visits: 44
yeah I have 2 PRODUCTION db's still running this version of SQL server...I have further altered the script to make it a dynamic SP and have set the spreadsheet up to be refreshable so i just pass the server in and hey presto, easy real time checking :)
Post #1467250
Posted Monday, May 12, 2014 6:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:42 PM
Points: 3, Visits: 75
Love this script and the excel template. Thanks.
I have modified the line below by adding @@servername. This gives me a clearer picture on which SQL instance the report was run.

from
SELECT col1 as DT,' + @cols + N'
to
SELECT col1 as '+@@SERVERNAME+'_DT,' + @cols + N'
Post #1570093
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse