Job Status Revised.

,

This procedure queries the sysjobs, sysjobschedules and sysjobhistory table to produce a resultset showing the jobs on a server plus their schedules(if applicable),how often the interval occurs(every X hours,min.,days,etc), the maximun duration, last duration,last status   of the job.


This is a script that I modified and added to from M.Pearson.

Create Procedure GetJobSchedule

AS

/*******************************************************************************

Name:			GetJobSchedule	(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.


Modification History:
-------------------------------------------------------------------------------
Version Date		Name		Modification
-------------------------------------------------------------------------------
1.0 	5  Jun 2002	M.Pearson	Inital Creation
1.1	15 Dec 2003     M. Austin	Added How often it occurs on and at what interval
1.2	16 Dec 2003	M. Austin	Added Last Duration and Last Run Status
1.2	16 Dec 2003	M. Austin	Made it a Procedure

*******************************************************************************/




SELECT dbo.sysjobs.Name AS 'Job Name', 
	'Job Enabled' = CASE dbo.sysjobs.Enabled
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END,
	'Schedule Enabled' = CASE 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',
'Last Run Status'=isnull(Case when Q2.run_status =1  then 'Succeeded' when Q2.run_status=0 then 'Failed' End,'NA')
FROM dbo.sysjobs 
LEFT OUTER JOIN dbo.sysjobschedules 
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
		FROM dbo.sysjobhistory
		GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
Left outer join (select T0.job_id,T0.run_status,'Last Duration'=T0.run_duration from sysjobhistory T0
inner join 
(select job_id,'Instance_id'=max(instance_id) FROM 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 dbo.sysjobs.job_id = Q2.job_id
WHERE Next_run_time = 0

UNION

SELECT dbo.sysjobs.Name AS 'Job Name', 
	'Job Enabled' = CASE dbo.sysjobs.Enabled
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END,
	'Schedule Enabled' = CASE 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',
'Last Run Status'=isnull(Case when Q2.run_status =1  then 'Succeeded' when Q2.run_status=0 then 'Failed' End,'NA')
FROM dbo.sysjobs 
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
		FROM dbo.sysjobhistory
		GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
Left outer join (select T0.job_id,T0.run_status,'Last Duration'=T0.run_duration from sysjobhistory T0
inner join 
(select job_id,'Instance_id'=max(instance_id) FROM 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 dbo.sysjobs.job_id = Q2.job_id
WHERE Next_run_time <> 0

ORDER BY [Start Date],[Start Time]

Rate

Share

Share

Rate