September 30, 2008 at 11:19 am
In SQL Server 2000, I had found a script that allowed me to obtain a listing of the schedule jobs on the server giving job name, schedule type, times, etc.
I am trying to do the same for a new 2005 instance being set up but I am not sure how to get what I am looking for.
Here is the script I used in 2000:
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]
into ##Temp
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
Can someone point me to something similiar for 2005?
Thanks!
~mj
September 30, 2008 at 12:44 pm
See attached, something I put together recently.
I also have a couple wrapper procedures to turn this into a report and be able to query any server in your environment.
Hope this helps.
September 30, 2008 at 12:50 pm
Take a look at msdb..sp_help_jobschedule. That might do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 30, 2008 at 12:54 pm
System stored procs aren't fun though ... to limited!
September 30, 2008 at 1:55 pm
Thank you Adam, This is the type of stuff I was looking for!
Grateful,
MJ
~mj
November 13, 2008 at 11:49 am
i think following tables will be useful to you:-
select * from msdb.dbo.sysschedules_localserver_view
select * from msdb.dbo.sysschedules
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply