USP_GetJobSchedules

Create Procedure usp_GetJobSchedules

@start varchar(20),
@end VarChar(20)

AS

/*******************************************************************************
Name:GetJobSchedules(For SQL Server7.0&2000)

Author:M.Pearson
Modified By:M.Austin 
Creation Date:5 Jun 2002
Version:1.0


Program Overview:This queries the sysjobs, sysjobschedules and sysjobhistory table to
produce a resultset showing the jobs on a server plus their schedules
(if applicable) and the maximun duration of the job.

The UNION join is to cater for jobs that have been scheduled but not yet
run, as this information is stored in the 'active_start...' fields of the 
sysjobschedules table, whereas if the job has already run the schedule 
information is stored in the 'next_run...' fields of the sysjobschedules table.

Future Enhancements:Provide ways to timeslice job history by month, day, year, etc

drop procedure usp_GetJobSchedule
exec usp_GetJobSchedule '20040101', '20040502'
Modification History:
-------------------------------------------------------------------------------
Version DateNameModification
-------------------------------------------------------------------------------
1.0 5  Jun 2002M.PearsonInital Creation
1.115 Dec 2003     M. AustinAdded How often it occurs on and at what interval
1.216 Dec 2003M. AustinAdded Last Duration and Last Run Status
1.216 Dec 2003M. AustinMade it a Procedure
1.316 Apr 2004R.SotkiewiczAdded Average job Duration
1.423 Apr 2004R. SotkiewiczCompute Avg run times between dates
*******************************************************************************/
--CREATE THE SCRATCHPAD AREA
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Jobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#Jobs]

CREATE TABLE [dbo].[#Jobs] (
[JobName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Job_ID] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JobEnabled] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ScheduleEnabled] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Frequency] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OccursEvery] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubFreqInterval] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartDate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartTime] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MaxDuration] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastDuration] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AverageDuration] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastRunStatus] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempJobDurations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#TempJobDurations]


CREATE TABLE [dbo].[#TempJobDurations] (
[job_ID] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Duration] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]

WAITFOR DELAY '00:00:02' --Wait for creation of tables to finish, because I'm paranoid

INSERT #JOBS
SELECT left(MSDB.dbo.sysjobs.Name, 50) AS 'Job Name', 
MSDB.dbo.sysjobs.Job_ID,
'Job Enabled' = CASE MSDB.dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Schedule Enabled' = CASE MSDB.dbo.sysjobschedules.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END, 
'Occurs Every'=freq_subday_interval,
'Sub Freq Interval'=  Case  freq_subday_type 
when 4 then 'Minutes' 
When 8 then 'Hours'
  End,
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' + 
substring(convert(varchar(15),active_start_date),5,2) + '/' + 
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 3 THEN cast('00:0' 
+ Left(right(active_start_time,3),1)  
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(active_start_time,5),1) 
+':' + Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2) 
+':' + Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
END,
isnull(CASE len(Q1.run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(Q1.run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q1.run_duration as char) as char (8))
WHEN 3 THEN cast('00:0' 
+ Left(right(Q1.run_duration,3),1)  
+':' + right(Q1.run_duration,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(Q1.run_duration,4),2)  
+':' + right(Q1.run_duration,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(Q1.run_duration,5),1) 
+':' + Left(right(Q1.run_duration,4),2)  
+':' + right(Q1.run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(Q1.run_duration,6),2) 
+':' + Left(right(Q1.run_duration,4),2)  
+':' + right(Q1.run_duration,2) as char (8))
END,'NA') as 'Max Duration',
isnull(CASE len(Q2.[Last Duration])
WHEN 1 THEN cast('00:00:0'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 3 THEN cast('00:0' 
+ Left(right(Q2.[Last Duration],3),1)  
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(Q2.[Last Duration],4),2)  
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(Q2.[Last Duration],5),1) 
+':' + Left(right(Q2.[Last Duration],4),2)  
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 6 THEN cast(Left(right(Q2.[Last Duration],6),2) 
+':' + Left(right(Q2.[Last Duration],4),2)  
+':' + right(Q2.[Last Duration],2) as char (8))
END,'NA') as 'Last Duration',

NULL,

'Last Run Status'=isnull(Case when Q2.run_status =1  then 'Succeeded' when Q2.run_status=0 then 'Failed' End,'NA')



FROM MSDB.dbo.sysjobs 
LEFT OUTER JOIN MSDB.dbo.sysjobschedules 
ON MSDB.dbo.sysjobs.job_id = MSDB.dbo.sysjobschedules.job_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM MSDB.dbo.sysjobhistory 
GROUP BY job_id) Q1
ON MSDB.dbo.sysjobs.job_id = Q1.job_id
Left outer join (select T0.job_id,T0.run_status,'Last Duration'=T0.run_duration from MSDB.dbo.sysjobhistory T0
inner join 
(select job_id,'Instance_id'=max(instance_id) FROM MSDB.dbo.sysjobhistory where  step_id=1  GROUP BY job_id ) T1
On
T0.job_id=T1.job_id and
T0.instance_id=T1.instance_id) Q2
ON MSDB.dbo.sysjobs.job_id = Q2.job_id
WHERE Next_run_time = 0

UNION

SELECT left(MSDB.dbo.sysjobs.Name, 50) AS 'Job Name',
MSDB.dbo.sysjobs.Job_ID,
'Job Enabled' = CASE MSDB.dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Schedule Enabled' = CASE MSDB.dbo.sysjobschedules.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END, 
'Occurs Every'=freq_subday_interval,
'Sub Freq Interval'=  Case  freq_subday_type 
when 4 then 'Minutes' 
When 8 then 'Hours'
  End,
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' + 
substring(convert(varchar(15),next_run_date),5,2) + '/' + 
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0' 
+ Left(right(next_run_time,3),1)  
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1) 
+':' + Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2) 
+':' + Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
END,'NA'),
isnull(CASE len(Q1.run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(Q1.run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q1.run_duration as char) as char (8))
WHEN 3 THEN cast('00:0' 
+ Left(right(Q1.run_duration,3),1)  
+':' + right(Q1.run_duration,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(Q1.run_duration,4),2)  
+':' + right(Q1.run_duration,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(Q1.run_duration,5),1) 
+':' + Left(right(Q1.run_duration,4),2)  
+':' + right(Q1.run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(Q1.run_duration,6),2) 
+':' + Left(right(Q1.run_duration,4),2)  
+':' + right(Q1.run_duration,2) as char (8))
END,'NA') as 'Max Duration',
isnull(CASE len(Q2.[Last Duration])
WHEN 1 THEN cast('00:00:0'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 3 THEN cast('00:0' 
+ Left(right(Q2.[Last Duration],3),1)  
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(Q2.[Last Duration],4),2)  
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(Q2.[Last Duration],5),1) 
+':' + Left(right(Q2.[Last Duration],4),2)  
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 6 THEN cast(Left(right(Q2.[Last Duration],6),2) 
+':' + Left(right(Q2.[Last Duration],4),2)  
+':' + right(Q2.[Last Duration],2) as char (8))
END,'NA') as 'Last Duration',

NULL, --PlaceHolder for AverageDuration

'Last Run Status'=isnull(Case when Q2.run_status =1  then 'Succeeded' when Q2.run_status=0 then 'Failed' End,'NA')




FROM MSDB.dbo.sysjobs 
LEFT OUTER JOIN MSDB.dbo.sysjobschedules ON MSDB.dbo.sysjobs.job_id = MSDB.dbo.sysjobschedules.job_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM MSDB.dbo.sysjobhistory 
GROUP BY job_id) Q1
ON MSDB.dbo.sysjobs.job_id = Q1.job_id
Left outer join (select T0.job_id,T0.run_status,'Last Duration'=T0.run_duration from MSDB.DBO.sysjobhistory T0
inner join 
(select job_id,'Instance_id'=max(instance_id) FROM MSDB.dbo.sysjobhistory where step_id=1  GROUP BY job_id ) T1
On
T0.job_id=T1.job_id and
T0.instance_id=T1.instance_id) Q2
ON MSDB.dbo.sysjobs.job_id = Q2.job_id
WHERE Next_run_time <> 0


ORDER BY [Start Date],[Start Time]

----------------------------------------------
-- PART II: Get Job Average Durations       --
----------------------------------------------
SET CONCAT_NULL_YIELDS_NULL OFF
SET NOCOUNT ON

truncate table #TempJobDurations

INSERT #TEMPJOBDURATIONS
Select a.job_ID,

CASE len(b.run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(b.run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(b.run_duration as char) as char (8))
WHEN 3 THEN cast('00:0' 
+ Left(right(b.run_duration,3),1)  
+':' + right(b.run_duration,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(b.run_duration,4),2)  
+':' + right(b.run_duration,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(b.run_duration,5),1) 
+':' + Left(right(b.run_duration,4),2)  
+':' + right(b.run_duration, 2) as char (8))
WHEN 6 THEN cast(Left(right(b.run_duration,6),2) 
+':' + Left(right(b.run_duration,4),2)  
+':' + right(b.run_duration,2) as char (8))
END AS Duration


from MSDB.dbo.sysjobs a, MSDB.dbo.sysjobhistory b
where a.job_ID = b.job_ID
and b.run_status = 1 -- Job succeeded
and Step_ID = 0 --(Job Outcome Reporting Step)

and a.job_ID IN (Select Job_ID from #jobs) -- Get job history that is current

and b.run_date Between @Start and @End

--------------------------------------------------------
-- PART III: COMPILE AVERAGE RUN TIMES FOR EACH JOB
--------------------------------------------------------
Declare @SecondsSUM float
Declare @MinutesSUM float
Declare @HoursSUM float

Declare @vchSecondsAVG Varchar(3)
Declare @vchMinutesAVG Varchar(3)
Declare @vchHoursAVG   Varchar(3)

Declare @RecordCount float
Declare @Total float
Declare @Job_ID varchar(40)

Declare Job_ID_Cursor CURSOR for
select distinct JOB_ID from #tempjobdurations
OPEN Job_ID_Cursor

FETCH NEXT FROM Job_ID_Cursor into @Job_ID

WHILE @@FETCH_STATUS = 0
BEGIN

Set @RecordCount = (select count(*) from #TempJobDurations where Job_ID = @Job_ID)

Set @SecondsSUM = (Select sum(cast(right(Duration, 2) AS INT)) from  #TempJobDurations  where Job_ID = @Job_ID)

Set @MinutesSUM = (Select 60 * sum(cast(substring(Duration, 4, 2) AS INT)) from  #TempJobDurations where Job_ID = @Job_ID)

Set @HoursSUM = (Select 3600 * sum(cast(substring(Duration, 1, 2) AS INT)) from  #TempJobDurations where Job_ID = @Job_ID)

Set @Total = @SecondsSUM + @MinutesSUM + @HoursSUM

set @vchHoursAVG   = cast(convert(INT, (@total/@recordCount)/ 3600) AS Varchar(5))
set @vchMinutesAVG = cast((cast((@total/@Recordcount) AS INT) % 3600) / 60 as varchar(5)) 
set @vchSecondsAVG = cast((cast((@total/@Recordcount) AS INT) % 3600) % 60 as varchar(5)) 

-------------------------------------------------------
-- Format Hours Output (Zero Padding)
-------------------------------------------------------
if (len(@vchHoursAVG) = 1)
BEGIN
set @vchHoursAVG = '0' + @vchHoursAVG
END

-------------------------------------------------------
-- Format Minutes Output (Zero Padding)
-------------------------------------------------------
if (len(@vchMinutesAVG) = 1)
BEGIN
set @vchMinutesAVG = '0' + @vchMinutesAVG
END

-------------------------------------------------------
-- Format Seconds Output (Zero Padding)
-------------------------------------------------------
if (len(@vchSecondsAVG) = 1)
BEGIN
set @vchSecondsAVG = '0' + @vchSecondsAVG
END

Update #jobs
Set AverageDuration = @vchHoursAVG +':'+ @vchMinutesAVG +':'+@vchSecondsAVG
where Job_ID = @Job_ID

--print @Job_ID +'   '+ @vchHoursAVG +':'+ @vchMinutesAVG +':'+@vchSecondsAVG

FETCH NEXT FROM Job_ID_Cursor into @Job_ID
END
Close Job_ID_Cursor
Deallocate Job_ID_Cursor

----------------------------------------------
-- Present results
----------------------------------------------
select * from #jobs

----------------------------------------------
-- End of session should drop all Temp 
-- tables, but.. just in case... (Paranoia)
----------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Jobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#Jobs]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempJobDurations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#TempJobDurations]

GO