Click here to monitor SSC
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 (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 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 (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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)
            Wink 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
      Wink

--***************************************************************************************
-- 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
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 719
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 (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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 (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

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

Thanks
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8648 Visits: 885
This looks like it's going to be fun. Thanks.
robert.sterbal 56890
robert.sterbal 56890
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 1076
Can I recommend that you add a SQL tab to the spreadsheet and include the notes with it?
MiguelSQL
MiguelSQL
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 1088
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-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 1088
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