Advanced T-SQL Question: Produce Backup Window Gantt Chart

I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookI'm not familiar with these tables but this should give you
the output you want
SELECT j.name +
CASE WHEN 0 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 1 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 2 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 3 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 4 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 5 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 6 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 7 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 8 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 9 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 10 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 11 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 12 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 13 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 14 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 15 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 16 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 17 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 18 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 19 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 20 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 21 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 22 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 23 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END
FROM sysjobs j
INNER JOIN (SELECT job_id,
DATEPART(hour,DATEADD(second,
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600,
'19000101')) AS JobStart,
DATEPART(hour,DATEADD(second,
last_run_duration +
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600,
'19000101')) AS JobEnd,
last_run_duration
FROM sysjobservers) s ON j.job_id = s.job_id|||> It might be nice to get historical
> information but I'm not sure where I'd find it.
You have 5 tables in msdb with historical information about backup. The name
of the tables all start
with backup. For instance, check out msdb.dbo.backupset.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message
news:1144417911.795292.287140@.z34g2000cwc.googlegroups.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>|||Corrected (and simplified) version that handles jobs spanning midnight
SELECT j.name +
CASE WHEN 0 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 1 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 2 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 3 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 4 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 5 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 6 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 7 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 8 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 9 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 10 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 11 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 12 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 13 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 14 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 15 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 16 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 17 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 18 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 19 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 20 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 21 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 22 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 23 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END
FROM sysjobs j
INNER JOIN (SELECT job_id,
last_run_time / 10000 AS JobStart,
(last_run_duration +
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600) / 3600 AS JobEnd
FROM sysjobservers) s ON j.job_id = s.job_id|||Roger,
yet another approach:
create function backup_mask(@.from_hour int, @.to_hour int)
returns varchar(26)
as
begin
declare @.idle_mask varchar(26)
declare @.busy_mask varchar(24)
set @.idle_mask = '('+replicate('_',24)+')'
set @.busy_mask = replicate('X',24)
return(
substring(@.idle_mask, 1, @.from_hour)+
substring(@.busy_mask, 1, @.to_hour - @.from_hour+1)+
substring(@.idle_mask, 2 + @.to_hour,25-@.to_hour))
end
go
-- unit test --
select dbo.backup_mask(1,5)
select dbo.backup_mask(3,15)
select dbo.backup_mask(1,24)
select dbo.backup_mask(19,24)
(XXXXX___________________)
(1 row(s) affected)
(__XXXXXXXXXXXXX_________)
(1 row(s) affected)
(XXXXXXXXXXXXXXXXXXXXXXXX)
(1 row(s) affected)
(__________________XXXXXX)
(1 row(s) affected)
go
drop function backup_mask
go|||And another, using Alexander's function as a start,
just because we have QUOTENAME and STUFF.
(Note that the behavior of QUOTENAME when the
second parameter is '(' is not documented.)
create function backup_mask(@.from_hour int, @.to_hour int)
returns varchar(26)
as
begin
declare @.idle_mask varchar(26)
declare @.busy_mask varchar(24)
set @.idle_mask = replicate('_',24)
set @.busy_mask = replicate('X',24)
declare @.duration int
set @.duration = @.to_hour - @.from_hour + 1
if @.duration <= 0
return quotename(@.idle_mask,'(')
return
quotename(stuff(@.idle_mask,@.from_hour,@.d
uration,left(@.busy_mask,@.duration)),
'(')
end
Alexander Kuznetsov wrote:

>Roger,
>yet another approach:
>create function backup_mask(@.from_hour int, @.to_hour int)
>returns varchar(26)
>as
>begin
>declare @.idle_mask varchar(26)
>declare @.busy_mask varchar(24)
>set @.idle_mask = '('+replicate('_',24)+')'
>set @.busy_mask = replicate('X',24)
>return(
>substring(@.idle_mask, 1, @.from_hour)+
>substring(@.busy_mask, 1, @.to_hour - @.from_hour+1)+
>substring(@.idle_mask, 2 + @.to_hour,25-@.to_hour))
>end
>go
>-- unit test --
>select dbo.backup_mask(1,5)
>select dbo.backup_mask(3,15)
>select dbo.backup_mask(1,24)
>select dbo.backup_mask(19,24)
>
>--
>(XXXXX___________________)
>(1 row(s) affected)
>
>--
>(__XXXXXXXXXXXXX_________)
>(1 row(s) affected)
>
>--
>(XXXXXXXXXXXXXXXXXXXXXXXX)
>(1 row(s) affected)
>
>--
>(__________________XXXXXX)
>(1 row(s) affected)
>
>go
>drop function backup_mask
>go
>
>|||Thanks folks, I may have some follow up questions but for now I'm going
to digest the information you've sent me and see what I can come up
with. Thanks again for the assist!