Job Status Revised.

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 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

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



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]