Use This SP to Generate a SQL Server Job Report

Running the Stored Procedure

The following stored procedure is best created in the master database, although it is not required. Once the stored procedure has been created, it is executed without any parameters.

CREATE PROCEDURE sp_JobSchedule_rpt


declare @x int, @y int, @z int
declare @counter smallint
declare @days varchar(100), @day varchar(10)
declare @Jname sysname, @freq_interval int, @JID varchar(50)

create table #temp (JID varchar(50), Jname sysname, 
Jdays varchar(100))
–This cursor runs throough all the jobs that have a weekly frequency running on different days
Declare C cursor for select Job_id, name, freq_interval from msdb..sysjobschedules
where freq_type = 8
Open C
Fetch Next from c into @JID, @Jname, @freq_interval
while @@fetch_status = 0
set @counter = 0
set @x = 64
set @y = @freq_interval
set @z = @y
set @days = ”
set @day = ”

while @y <> 0
select @y = @y – @x
select @counter = @counter + 1
If @y < 0 
set @y = @z
GOTO start

Select @day = CASE @x
when 1 Then ‘Sunday’
when 2 Then ‘Monday’
when 4 Then ‘Tuesday’
when 8 Then ‘Wednesday’
when 16 Then ‘Thursday’
when 32 Then ‘Friday’
when 64 Then ‘Saturday’

select @days = @day + ‘,’ + @days
Select @x = CASE @counter
When 1 then 32
When 2 then 16
When 3 then 8
When 4 then 4
When 5 then 2
When 6 then 1

set @z = @y
if @y = 0 break

Insert into #temp select @jid, @jname, left(@days, len(@days)-1)
Fetch Next from c into @jid, @Jname, @freq_interval

close c
deallocate c

–Final query to extract complete information by joining sysjobs, sysjobschedules and #Temp table

select Job_Name, 
CASE b.enabled 
when 1 then ‘Enabled’
Else ‘Disabled’
End as JobEnabled, Schedule_Name, 

CASE a.enabled 
when 1 then ‘Enabled’
Else ‘Disabled’
End as ScheduleEnabled,

CASE freq_type 
when 1 Then ‘Once’
when 4 Then ‘Daily’
when 8 then ‘Weekly’
when 16 Then ‘Monthly’ –+ cast(freq_interval as char(2)) + ‘th Day’
when 32 Then ‘Monthly Relative’
when 64 Then ‘Execute When SQL Server Agent Starts’
End as [Job Frequency],

CASE freq_type 
when 32 then CASE freq_relative_interval
when 1 then ‘First’
when 2 then ‘Second’
when 4 then ‘Third’
when 8 then ‘Fourth’
when 16 then ‘Last’
Else ”
End as [Monthly Frequency],

CASE freq_type
when 16 then cast(freq_interval as char(2)) + ‘th Day of Month’
when 32 then CASE freq_interval 
when 1 then ‘Sunday’
when 2 then ‘Monday’
when 3 then ‘Tuesday’
when 4 then ‘Wednesday’
when 5 then ‘Thursday’
when 6 then ‘Friday’
when 7 then ‘Saturday’
when 8 then ‘Day’
when 9 then ‘Weekday’
when 10 then ‘Weekend day’
when 8 then c.Jdays
Else ”
End as [Runs On],
CASE freq_subday_type
when 1 then ‘At the specified Time’
when 2 then ‘Seconds’
when 4 then ‘Minutes’
when 8 then ‘Hours’
End as [Interval Type], CASE freq_subday_type 
when 1 then 0
Else freq_subday_interval 
End as [Time Interval],
CASE freq_type 
when 8 then cast(freq_recurrence_factor as char(2)) + ‘ Week’
when 16 Then cast(freq_recurrence_factor as char(2)) + ‘ Month’
when 32 Then cast(freq_recurrence_factor as char(2)) + ‘ Month’
Else ”
End as [Occurs Every],

left(active_start_date,4) + ‘-‘ + substring(cast(active_start_date as char),5,2) 
+ ‘-‘ + right(active_start_date,2) [Begin Date-Executing Job], 

left(REPLICATE(‘0’, 6-len(active_start_time)) + cast(active_start_time as char(6)),2) + ‘:’ +
substring(REPLICATE(‘0’, 6-len(active_start_time)) + cast(active_start_time as char(6)),3,2) + ‘:’ +
substring(REPLICATE(‘0’, 6-len(active_start_time)) + cast(active_start_time as char(6)),5,2)
[Executing At],

left(active_end_date,4) + ‘-‘ + substring(cast(active_end_date as char),5,2) 
+ ‘-‘ + right(active_end_date,2) [End Date-Executing Job],

left(REPLICATE(‘0’, 6-len(active_end_time)) + cast(active_end_time as char(6)),2) + ‘:’ +
substring(REPLICATE(‘0’, 6-len(active_end_time)) + cast(active_end_time as char(6)),3,2) + ‘:’ +
substring(REPLICATE(‘0’, 6-len(active_end_time)) + cast(active_end_time as char(6)),5,2)
[End Time-Executing Job],

b.date_created [Job Created], a.date_created [Schedule Created] 
from msdb..sysjobschedules a RIGHT OUTER JOIN msdb..sysjobs b ON a.job_id = b.job_id
LEFT OUTER JOIN #temp c on = c.jname and a.job_id = c.Jid

Order by 1

Drop Table #Temp


Published with the express written permission of the author. Copyrighted 2002.

Pages: 1 2


No comments yet... Be the first to leave a reply!