Today i was going through few blogs and i found a very interesting procedure which can be used to generate a SQL Server Job Report. I have copied the contents from http://www.sql-server-performance.com/2002/job-report/1/.
CREATE PROCEDURE sp_JobSchedule_rpt
AS
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)
SET NOCOUNT ON
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
Begin
set @counter = 0
set @x = 64
set @y = @freq_interval
set @z = @y
set @days = ”
set @day = ”
while @y <> 0
begin
select @y = @y – @x
select @counter = @counter + 1
If @y < 0
Begin
set @y = @z
GOTO start
End
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’
End
select @days = @day + ‘,’ + @days
start:
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
End
set @z = @y
if @y = 0 break
end
Insert into #temp select @jid, @jname, left(@days, len(@days)-1)
Fetch Next from c into @jid, @Jname, @freq_interval
End
close c
deallocate c
–Final query to extract complete information by joining sysjobs, sysjobschedules and #Temp table
select b.name Job_Name,
CASE b.enabled
when 1 then ‘Enabled’
Else ‘Disabled’
End as JobEnabled, a.name 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’
End
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’
End
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 a.name = c.jname and a.job_id = c.Jid
Order by 1
Drop Table #Temp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Have you ever wanted to create a report listing all of the SQL Server jobs on your SQL Server 7.0 or 2000 servers? This is easy if you use the stored procedure I have created just for this purpose. The stored procedure, called sp_JobSchedule_rpt, creates this report by joining the sysjobs and sysjobschedules tables and creating a resultset.
To make the report easy to read, I have had to substitute the various integer values found in these tables with readable values. The first part of the stored procedure generates the list of jobs and the days to run for weekly scheduled jobs. The second part is written with CASE statements to incorporate all the possible combinations listed under the sysjobschedules table.
Making the Data Readable
The following are the various numeric entries and their string values, used in the stored procedure, to make it more readable.
freq_type int Frequency of the schedule execution:
1 = Once
4 = Daily
8 = Weekly
16 = Monthly
32 = Monthly relative
64 = Execute when SQL Server
freq_interval int Value indicating on which days the schedule runs.
If freq_type is 4 (daily), the value is every freq_interval days.
If freq_type is 8 (weekly), the value is a bitmask indicating the days in which weekly schedules are run. The freq_interval values are:
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday
If freq_type is 16 (monthly), the value is freq_interval day of the month.
If freq_type is 32 (monthly relative), freq_interval can be one of these values:
1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday
8 = Day
9 = Weekday
10 = Weekend day
freq_subday_type int Units for the freq_subday_interval:
1 = At the specified time
2 = Seconds
4 = Minutes
8 = Hours
freq_subday_interval int Number of freq_subday_type periods to occur between each scheduled execution of the job.
freq_relative_interval int Scheduled job’s occurrence of the freq_interval in each month when freq_type is 32 (monthly relative):
1 = First
2 = Second
4 = Third
8 = Fourth
16 = Last
freq_recurrence_factor int Number of weeks or months between the scheduled execution of the job.
freq_type int Frequency of the schedule execution:
1 = Once
4 = Daily
8 = Weekly
16 = Monthly
32 = Monthly relative
64 = Execute when SQL Server
freq_interval int Value indicating on which days the schedule runs.
If freq_type is 4 (daily), the value is every freq_interval days.
If freq_type is 8 (weekly), the value is a bitmask indicating the days in which weekly schedules are run. The freq_interval values are:
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday
If freq_type is 16 (monthly), the value is freq_interval day of the month.
If freq_type is 32 (monthly relative), freq_interval can be one of these values:
1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday
8 = Day
9 = Weekday
10 = Weekend day
freq_subday_type int Units for the freq_subday_interval:
1 = At the specified time
2 = Seconds
4 = Minutes
8 = Hours
freq_subday_interval int Number of freq_subday_type periods to occur between each scheduled execution of the job.
freq_relative_interval int Scheduled job’s occurrence of the freq_interval in each month when freq_type is 32 (monthly relative):
1 = First
2 = Second
4 = Third
8 = Fourth
16 = Last
freq_recurrence_factor int Number of weeks or months between the scheduled execution of the job.
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
AS
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)
SET NOCOUNT ON
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
Begin
set @counter = 0
set @x = 64
set @y = @freq_interval
set @z = @y
set @days = ”
set @day = ”
while @y <> 0
begin
select @y = @y – @x
select @counter = @counter + 1
If @y < 0
Begin
set @y = @z
GOTO start
End
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’
End
select @days = @day + ‘,’ + @days
start:
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
End
set @z = @y
if @y = 0 break
end
Insert into #temp select @jid, @jname, left(@days, len(@days)-1)
Fetch Next from c into @jid, @Jname, @freq_interval
End
close c
deallocate c
–Final query to extract complete information by joining sysjobs, sysjobschedules and #Temp table
select b.name Job_Name,
CASE b.enabled
when 1 then ‘Enabled’
Else ‘Disabled’
End as JobEnabled, a.name 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’
End
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’
End
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 a.name = c.jname and a.job_id = c.Jid
Order by 1
Drop Table #Temp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
No comments:
Post a Comment