March 26, 2006 at 10:27 am
What I'm hoping to achieve is be able to query one table that will give me timings of a particular job that runs daily. For example I have a Start_Job_Time and End_Job_Time. I'd like to get the average timings say over a month or two and aslo get the low and high times with the corresponding dates from the Start_Job_Time End_Job_Time fields.
As a bonus it would be good if I could populate the extracted timings to a "job timings table".
Appreciate suggestions,
March 26, 2006 at 11:25 am
This should give you what you need, just need to doctor the script to insert into a reporting table, use a where clause on the insert where last run date not in reporting table. I believe I got this script from here in the past.
set nocount on
go
use msdb
go
set rowcount 0
declare @job_id varchar(200),
@sched_id varchar(200),
@freq_type int,
@Freq_interval int,
@freq_subday_type int,
@freq_subday_interval int,
@freq_relative_interval int,
@freq_recurrence_factor int,
@active_start_date int,
@schedule_word varchar(1000),
@schedule_day varchar(200),
@conv_start_time char(6),
@conv_end_time char(6)
create table #joblistings
(job_id varchar(200),
sched_id varchar(200),
job_name sysname,
-- sched_name sysname null,
Status int,
Scheduled int null,
schedule_id int null,
schedule_word varchar(1000) null,
freq_type int null,
freq_interval int null,
freq_subday_type int null,
freq_subday_interval int null,
freq_relative_interval int null,
freq_recurrence_factor int null,
active_start_date int null,
active_end_date int null,
active_start_time int null,
active_end_time int null,
date_created datetime null,
description nvarchar(512) null,
last_run_date int null,
last_run_time int null,
last_run_duration int null,
last_outcome_message nvarchar(1024))
insert into #joblistings (job_id,
sched_id ,
job_name ,
-- sched_name ,
Status ,
Scheduled ,
schedule_id ,
schedule_word ,
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
freq_relative_interval,
freq_recurrence_factor,
active_start_date,
active_end_date,
active_start_time,
active_end_time,
date_created,
description,
sjs.last_run_date,
last_run_time,
last_run_duration,
last_outcome_message)
select j.job_id,
c.schedule_id,
j.name ,
-- c.name ,
j.enabled,
c.enabled,
c.schedule_id,
null,
c.freq_type,
c.freq_interval,
c.freq_subday_type,
c.freq_subday_interval,
c.freq_relative_interval,
c.freq_recurrence_factor,
c.active_start_date,
c.active_end_date,
c.active_start_time,
c.active_end_time,
j.date_created,
j.description,
sjs.last_run_date,
sjs.last_run_time,
sjs.last_run_duration,
sjs.last_outcome_message
from sysjobs j (nolock) inner join
sysjobschedules c (nolock)
on j.job_id = c.job_id
inner join sysjobservers sjs (nolock)
on j.job_id = sjs.job_id
while 1=1
begin
set rowcount 0
set @schedule_word = ''
if (select count(*) from #joblistings where Scheduled=1 and schedule_word is null) = 0
break
else
begin
set rowcount 1
select @job_id=job_id,
@sched_id=sched_id,
@freq_type=freq_type,
@Freq_interval=freq_interval,
@freq_subday_type=freq_subday_type,
@freq_subday_interval=freq_subday_interval,
@freq_relative_interval=freq_relative_interval,
@freq_recurrence_factor=freq_recurrence_factor,
@active_start_date = active_start_date,
@conv_start_time = case
when len(active_start_time) < 6 then replicate('0',6-(len(active_start_time))) + cast(active_start_time as varchar(5))
else cast(active_start_time as varchar(6))
end,
@conv_end_time= case
when len(active_end_time) < 6 then replicate('0',6-(len(active_end_time))) + cast(active_end_time as varchar(5))
else cast(active_end_time as varchar(6))
end
from #joblistings
where schedule_word is null
and Scheduled=1
if exists(select @freq_type where @freq_type in (1,64))
begin
select @schedule_word = case @freq_type
when 1 then 'Occurs Once, On '+cast(@active_start_date as varchar(8))+', At '+@conv_start_time
when 64 then 'Occurs When SQL Server Agent Starts'
end
end
else
begin
if @freq_type=4
begin
select @schedule_word = 'Occurs Every '+cast(@Freq_interval as varchar(10))+' Day(s)'
end
if @freq_type=8
begin
select @schedule_word = 'Occurs Every '+cast(@freq_recurrence_factor as varchar(3))+' Week(s)'
select @schedule_day=''
if (SELECT (convert(int,(@Freq_interval/1)) % 2)) = 1
select @schedule_day = @schedule_day+'Sun'
if (SELECT (convert(int,(@Freq_interval/2)) % 2)) = 1
select @schedule_day = @schedule_day+'Mon'
if (SELECT (convert(int,(@Freq_interval/4)) % 2)) = 1
select @schedule_day = @schedule_day+'Tue'
if (SELECT (convert(int,(@Freq_interval/8)) % 2)) = 1
select @schedule_day = @schedule_day+'Wed'
if (SELECT (convert(int,(@Freq_interval/16)) % 2)) = 1
select @schedule_day = @schedule_day+'Thu'
if (SELECT (convert(int,(@Freq_interval/32)) % 2)) = 1
select @schedule_day = @schedule_day+'Fri'
if (SELECT (convert(int,(@Freq_interval/64)) % 2)) = 1
select @schedule_day = @schedule_day+'Sat'
select @schedule_word = @schedule_word+', On '+@schedule_day
end
if @freq_type=16
begin
select @schedule_word = 'Occurs Every '+cast(@freq_recurrence_factor as varchar(3))+' Month(s) on Day '+cast(@Freq_interval as varchar(3))+' of that Month'
end
if @freq_type=32
begin
select @schedule_word = case @freq_relative_interval
when 1 then 'First'
when 2 then 'Second'
when 4 then 'Third'
when 8 then 'Fourth'
when 16 then 'Last'
end
select @schedule_word =
case @Freq_interval
when 1 then 'Occurs Every '+@schedule_word+' Sunday of the Month'
when 2 then 'Occurs Every '+@schedule_word+' Monday of the Month'
when 3 then 'Occurs Every '+@schedule_word+' Tueday of the Month'
when 4 then 'Occurs Every '+@schedule_word+' Wednesday of the Month'
when 5 then 'Occurs Every '+@schedule_word+' Thursday of the Month'
when 6 then 'Occurs Every '+@schedule_word+' Friday of the Month'
when 7 then 'Occurs Every '+@schedule_word+' Saturday of the Month'
when 8 then 'Occurs Every '+@schedule_word+' Day of the Month'
when 9 then 'Occurs Every '+@schedule_word+' Weekday of the Month'
when 10 then 'Occurs Every '+@schedule_word+' Weekend Day of the Month'
end
end
select @schedule_word =
case @freq_subday_type
when 1 then @schedule_word+', At '+@conv_start_time
when 2 then @schedule_word+', every '+cast(@freq_subday_interval as varchar(3))+' Second(s) Between '+@conv_start_time+' and '+@conv_end_time
when 4 then @schedule_word+', every '+cast(@freq_subday_interval as varchar(3))+' Minute(s) Between '+@conv_start_time+' and '+@conv_end_time
when 8 then @schedule_word+', every '+cast(@freq_subday_interval as varchar(3))+' Hour(s) Between '+@conv_start_time+' and '+@conv_end_time
end
end
end
update #joblistings
set schedule_word=@schedule_word
where job_id=@job_id
set rowcount 0
end
select job_id,
@@servername as ServerName,
getdate() as Report_Date,
job_name ,
-- sched_name ,
description,
Status = case Status
when 1 then 'Enabled'
when 0 then 'Disabled'
else ' '
end,
Scheduled= case Scheduled
when 1 then 'Yes'
when 0 then 'No'
else ' '
end,
schedule_id,
schedule_word as 'Frequency' ,
active_start_date,
active_end_date,
date_created,
last_run_date,
last_run_time,
last_run_duration,
last_outcome_message
from #joblistings
order by
Status desc,
Scheduled desc,
job_name
drop table #joblistings
March 26, 2006 at 4:29 pm
select Job_Id, MIN(End_Job_Time - Start_Job_Time) as MinDuration,
MAX(End_Job_Time - Start_Job_Time) as MAXDuration,
AVG(End_Job_Time - Start_Job_Time) as AVGDuration,
YEAR(Start_Job_Time) as StartYear, MONTH(Start_Job_Time) as StartMonth
FROM JobRecords
GROUP BY Job_id, YEAR(Start_Job_Time), MONTH(Start_Job_Time)
Use it as subquery to find out which job did have MAX MaxDuration, MAX AvgDuration, etc.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply