SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Job Timeline (Graphical)


SQL Job Timeline (Graphical)

Author
Message
The Wizard Of Oz
The Wizard Of Oz
SSC-Addicted
SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)

Group: General Forum Members
Points: 487 Visits: 314
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!
ryanwooster1980
ryanwooster1980
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 46
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;

Theo Ekelmans
Theo Ekelmans
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 739
Thanks ryanwooster1980 :-D

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.
ryanwooster1980
ryanwooster1980
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 46
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 Smile
alex.lam 53610
alex.lam 53610
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 95
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'
m mcdonald
m mcdonald
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1903 Visits: 1684
Very Nice with some useful tweaks in the thread.

Thanks
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13962 Visits: 885
This looks like it's going to be fun. Thanks.
robert.sterbal 56890
robert.sterbal 56890
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1005 Visits: 1136
Can I recommend that you add a SQL tab to the spreadsheet and include the notes with it?
MiguelSQL
MiguelSQL
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 1136
I love it.... and coincidentally I was working in the same problem but from a Powershell angle. Now I don't have to.
BTW... why do you have this:
your your WHERE statement:

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



Is that left over from testing?
MiguelSQL
MiguelSQL
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 1136
argg... that's supposed to be <dbname>
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search