The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

runtime of Jobs scheduled to run multiple times day on SQL Server 2005


I have developed a script to review the runtime of Jobs scheduled to run multiple times day on SQL Server 2005.

the script has been developed on SQL server 2005 and gives the ability to find out from all active jobs the precentage of running time divided to job interval in the last 24 hours.

couple of remarks:

1) Does not work in SQL Server 2000 because sp_help_jobschedule returns less columns in 2000
2) Does not support multiple Job schedules such as onetimes
3) Only list jobs that are scheduled to run multiple times a day

You can also find the post here:

http://www.sqlservercentral.com/scripts/Administration/63245/

The ones who does not have an account at sqlservercentral.com, here is the script:

USE [msdb]
GO
/*******************************************************
created by: Tomer Lev / Israel / SQL Server DBA
date: 28/05/2008
out put:
Run Date – job running date
Job Name
Average running time/Interval
Min running time/Interval
Max running time/Interval
stdev running time/Interval
*****************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[JobsTimeIntervalCheckingTime]
AS

create table #tempschedule
(
id int identity(1,1) ,
schedule_id int,
schedule_name sysname,
enabled int,
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,
active_end_date int,
active_start_time int,
active_end_time int,
date_created datetime,
schedule_description nvarchar(4000),
next_run_date int,
next_run_time int,
schedule_uid uniqueidentifier,
jobcoubnt int
)

create table #helpschedule
(
jobname sysname,
freq_type int,
freq_subday_type int,
freq_subday_interval int
)

declare @jobnametemp sysname
declare @schedule_id int

DECLARE CursorQuery CURSOR FOR
SELECT [name] FROM sysjobs WHERE [enabled] = 1

OPEN CursorQuery
FETCH NEXT FROM CursorQuery
INTO @jobnametemp
PRINT ‘Record Status’ + CAST(@@FETCH_STATUS as varchar)
WHILE @@FETCH_STATUS = 0
BEGIN
insert #tempschedule exec msdb..sp_help_jobschedule @job_name=@jobnametemp
insert #helpschedule select @jobnametemp,freq_type,freq_subday_type,freq_subday_interval from #tempschedule
where id = @@identity
FETCH NEXT FROM CursorQuery
INTO @jobnametemp
END
CLOSE CursorQuery
DEALLOCATE CursorQuery

drop table #tempschedule

select
[Run Date],
[Job Name],
avg([RunningTime/Interval])*100 as [Avg RunningTime/Interval],
min([RunningTime/Interval])*100 as [Min RunningTime/Interval],
max([RunningTime/Interval])*100 as [Max RunningTime/Interval],
stdev([RunningTime/Interval])*100 as [stdev RunningTime/Interval],
count(*) as [count]
from(
select
[Run Date],
[Job Name],
(seconds + minutes * 60 + hours * 3600) [total seconds runnning time],
([frequency_sub_type] * [freq_subday_interval]) as [interval Time in seconds],
cast((seconds + minutes * 60 + hours * 3600) as float) / cast(([frequency_sub_type] * [freq_subday_interval]) as float) [RunningTime/Interval]
from(
SELECT CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) [Run Date],
SUBSTRING(T2.name,1,40) [Job Name],
T1.[run_duration],
len(T1.[run_duration]) [len],
case len(T1.[run_duration])
when 4 then right(T1.[run_duration],2)
when 5 then right(T1.[run_duration],2)
when 6 then right(T1.[run_duration],2)
else T1.[run_duration] end [seconds],
case len(T1.[run_duration])
when 1 then 0.00001
when 2 then 0.00001
when 3 then 0.00001
when 4 then left(T1.[run_duration],2)
when 5 then substring(cast(T1.[run_duration] as varchar),2,2)
when 6 then substring(cast(T1.[run_duration] as varchar),3,2)
else T1.[run_duration] end [minutes],
case len(T1.[run_duration])
when 1 then 0.00001
when 2 then 0.00001
when 3 then 0.00001
when 4 then 0.00001
when 5 then substring(cast(T1.[run_duration] as varchar),1,1)
when 6 then substring(cast(T1.[run_duration] as varchar),1,2)
else T1.[run_duration] end [hours],
case(h.freq_type)
when 1 then ‘Once’
when 4 then ‘Daily’
when 8 then ‘Weekly’
when 16 then ‘Monthly ‘
when 32 then ‘Monthly, relative to the freq_interval’
when 64 then ‘Run when SQLServerAgent service starts’ end [frequency_type],
case(h.freq_subday_type)
when 4 then 60
when 1 then 1
when 8 then 3600 end [frequency_sub_type], — min, sec, hours
h.freq_subday_interval — time interval
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
join #helpschedule h
on T2.name = h.jobname
where T2.enabled = 1 and T1.run_status not in (2,3,4) and T1.step_id = 0
AND run_date >= CONVERT(char(8), (select dateadd (day,-1,getdate())), 112))q
where [frequency_sub_type] * [freq_subday_interval] !=0
)
q2
group by [Run Date],[Job Name]

drop table #helpschedule

go

exec JobsTimeIntervalCheckingTime

Enjoy,
Tomer Lev

Advertisements

June 11, 2009 - Posted by | Administration, SQL SERVER | , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: