I am looking for a script that will show me the job schedule for the jobs on a given server, in plain english. It would look something like this:
Job Name Schedule
Job 1 M,Tu,W,Th,F 3:10 pm
Job 2 1st Day Month 6:00 am
Job 3 Daily 4:15 pm
I have looked at the job schedule table and have decipher some of it, but not all. Can anyone point me in the right direction?
Thank you!!
Jim Youmans
St. Louis, Missouri, USA
Hi,
I would need some help on that one too.
I came-up with a dirty draft script and cannot figure out a systematic way to decipher the codes.
i.e. convert date would be fairly simple, I guess convert times as well if I knew the definition of this time.
As for the frequency codes, I am only able to guess, I do not know the exact decode table.
freq_type of 1 is for a one time run, freq_time of 8 is for a day of week
freq_interval of 64 is for Saturday, freq_interval of 124 is for Tue to Sat. 72 is for Wed and Sat
I am sorry I do not have the key, however here is the starting script for this. This script could use more views I guess but so far this is all I have.
Thanks to point us to the correct direction
Philippe
SELECT
a.name AS job_name,
a.enabled,
a.description,
a.date_created,
a.date_modified,
b.last_outcome_message,
b.last_run_date,
b.last_run_time,
b.last_run_duration,
s.next_run_date,
s.next_run_time,
v.name,
v.freq_type,
v.freq_subday_type,
v.freq_interval
FROM
MSDB.dbo.sysjobs AS a
INNER JOIN
MSDB.dbo.sysjobservers AS b
ON a.job_id = b.job_id
INNER JOIN
MSDB.dbo.syscategories AS c
ON a.category_id = c.category_id
INNER JOIN
MSDB.dbo.sysjobschedules AS S
ON A.job_id = s.job_id
INNER JOIN
MSDB.dbo.sysschedules_localserver_view AS v
ON s.schedule_id = v.schedule_id
WHERE
(c.name <> 'Report Server' )
ORDER BY
job_name
|||Hi,
I have coded something similar, but there is help in the technet stuff.
http://technet.microsoft.com/en-us/library/ms187320.aspx
I did have a look at this and the difficult thing you will find if trying to code for every combination, you'll need to translate the freq_interval into binary ineffect.
So 1 = 0000001 = Sunday
2 = 0000010 = Monday
3 = 0000011 = Sunday and Monday
4 = 0000100 = Tuesday
...
128 = 1111111 = Sunday, Monday,Tuesday,Wednesday,Thursday,Friday,Saturday
The time is below, need to break down the number into the different parts 083023 would be 08:30:23. 24 hour clock.
select distinct
j.name,jbs.freq_type, JbS.freq_interval,
case jbs.freq_type when 4 then 'Daily' when 16 then 'Monthly' when 32 then 'Monthly' when 8 then
(CASE JbS.freq_interval when 1 then 'Weekly' when 2 then 'Weekly' when 4 then 'Weekly' when 8 then
'Weekly' when 16 then 'Weekly' when 32 then 'Weekly' when 64 then 'Weekly' else 'Daily' end) end as Update_Type,
case when js.next_run_time = 0 OR js.next_run_time is null then '0' else left(js.next_run_time,len(js.next_run_time)-4) +':'+ substring(cast(js.next_run_time as char),len(js.next_run_time)-4+1,2)+':'+right(js.next_run_time,2) end
from
msdb..sysjobs j
left outer join msdb..sysjobschedules js
on js.job_id = j.job_id
left outer join msdb..sysschedules jbs
on jbs.schedule_id = js.schedule_id
where
j.enabled = 1
and j.category_id = 0
and jbs.enabled = 1
I did do some other things around these tables if you have any more questions. Hopefully that will help you out.
Cheers
Matt
|||Thanks for the help. I found what I need at
http://www.sqlmag.com/Article/ArticleID/15560/sql_server_15560.html
Jim Youmans
St. Louis, MO USA
|||Hi,
Thanks for the info. I also added some more specific information like complete Schedule description.
Now I have the procedure I need for SQL 2005.
Here it is.
Regards,
Philippe
-- =============================================
-- Create procedure up_JobInformation
-- Revision 0 for SQL2005 and ReportingServices
-- Aug-30-07
-- Philippe Cand
--
-- Use the system sp msdb.dbo.sp_get_schedule_description
-- try to use views as much as possible however system tables cannot be avoided
-- There may be room for improvement like avoiding implicit conversions
-- It may also be possible to convert time at the end of job description
-- This time is visible in the Next run time column so I skip this option.
-- since jobs are not many, the Wile loop is fast enough.
-- This procedure target the local server.
-- Centralized reporting for multiple servers would require another code
-- =============================================
USE ONGlobals -- I prefer have this procedure in a user database
GO
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'up_JobInformation'
)
DROP PROCEDURE dbo.up_JobInformation
GO
CREATE PROCEDURE dbo.up_JobInformation
@.Enabled int = 99
, @.Job varchar(128) = '##'
, @.Freq varchar(85) = '##'
AS
BEGIN
SET NOCOUNT ON
Declare
@.TotCount as Int
, @.Counter as int
, @.JobID as uniqueidentifier
, @.SchedID as int
, @.freq_type_Value as int
, @.freq_interval_Value as int
, @.freq_subday_type_Value as int
, @.freq_subday_interval_Value as int
, @.freq_relative_interval_Value as int
, @.freq_recurrence_factor_Value as int
, @.active_start_date_Value as int
, @.active_end_date_Value as int
, @.active_start_time_Value as int
, @.active_end_time_Value as int
, @.schedule_description_Value as nvarchar(255)
CREATE TABLE #Jobs (RecNum tinyint identity(1, 1), JobID uniqueidentifier, SchedID int)
INSERT INTO #Jobs (JobID, SchedID)
SELECT jv.job_id , sv.schedule_id
FROM msdb.dbo.sysjobs_view As jv
INNER JOIN
MSDB.dbo.syscategories AS sc
ON jv.category_id = sc.category_id
INNER JOIN
MSDB.dbo.sysjobschedules AS S
ON jv.job_id = s.job_id
INNER JOIN
MSDB.dbo.sysschedules_localserver_view AS sv
ON s.schedule_id = sv.schedule_id
WHERE (@.Enabled = 99 or jv.enabled = @.Enabled)
AND (@.Job = '##' or jv.name Like @.Job)
AND (sc.name <> 'Report Server')
AND (sv.enabled = 1)
SELECT @.TotCount = (SELECT count(*) FROM #Jobs)
, @.Counter = 1
CREATE TABLE #JobReport
( Job_Name nvarchar(128), Enabled tinyint, Description nvarchar(512)
, date_Created datetime, date_modified datetime
, Last_outcome_Message nvarchar(1024)
, Last_Run_Date int, last_Run_Time int
, Last_Run_Duration int
, Next_run_date int, Next_run_time int
, Schedule_Name nvarchar(128)
, Schedule_Description nvarchar(250)
)
WHILE @.Counter > 0 AND @.Counter <= @.TotCount
BEGIN
SELECT
@.JobID = j.JobID
, @.SchedID = j.SchedID
, @.freq_type_Value = sv.freq_type
, @.freq_interval_Value = sv.freq_interval
, @.freq_subday_type_Value = sv.freq_subday_type
, @.freq_subday_interval_Value = sv.freq_subday_interval
, @.freq_relative_interval_Value = sv.freq_relative_interval
, @.freq_recurrence_factor_Value = sv.freq_recurrence_factor
, @.active_start_date_Value = sv.active_start_date
, @.active_end_date_Value = sv.active_end_date
, @.active_start_time_Value = sv.active_start_time
, @.active_end_time_Value = sv.active_end_time
FROM #Jobs j
INNER JOIN
MSDB.dbo.sysschedules_localserver_view sv on j.SchedID = sv.schedule_id
WHERE j.RecNum = @.Counter
Exec msdb.dbo.sp_get_schedule_description
@.freq_type_Value,
@.freq_interval_Value,
@.freq_subday_type_Value,
@.freq_subday_interval_Value,
@.freq_relative_interval_Value,
@.freq_recurrence_factor_Value,
@.active_start_date_Value,
@.active_end_date_Value,
@.active_start_time_Value,
@.active_end_time_Value ,
@.schedule_description_Value OUTPUT
Insert into #JobReport
( Job_Name , Enabled , Description
, date_Created , date_modified
, Last_outcome_Message
, Last_Run_Date , last_Run_Time
, Last_Run_Duration
, Next_run_date , Next_run_time
, Schedule_Name
, Schedule_Description
)
SELECT
a.name AS job_name,
a.enabled,
a.description,
a.date_created,
a.date_modified,
b.last_outcome_message,
b.last_run_date,
b.last_run_time,
b.last_run_duration,
s.next_run_date,
s.next_run_time,
v.name as Schedule_Name ,
@.schedule_description_Value
FROM
msdb.dbo.sysjobs_view As a
INNER JOIN
MSDB.dbo.sysjobservers AS b
ON a.job_id = b.job_id
INNER JOIN
MSDB.dbo.syscategories AS c
ON a.category_id = c.category_id
INNER JOIN
MSDB.dbo.sysjobschedules AS S
ON A.job_id = s.job_id
INNER JOIN
MSDB.dbo.sysschedules_localserver_view AS v
ON s.schedule_id = v.schedule_id
WHERE
(a.job_id = @.JobID AND v.schedule_id = @.SchedID)
SET @.Counter = @.Counter + 1
END -- end while
SELECT
Job_Name , Enabled , Description
, date_Created , date_modified
, Last_outcome_Message
, Substring(Cast(Last_Run_Date as char(8)),5,2) + '/'
+ Cast(Right(Last_Run_Date,2) as char(2)) + '/'
+ Cast(Left(Last_Run_Date,4) as char(4)) as Last_Run_Date
, Cast(left(Right(Stuff(last_Run_Time,1,0,'0'),6),2) as varchar(2)) + ':'
+ Cast(Left(Right(last_Run_Time,4),2) as char(2)) + ':'
+ Cast(right(last_Run_Time,2) as char(2)) as last_Run_Time
, Left(
stuff(Last_Run_Duration,1,0,replicate('0',6 - len(Last_Run_Duration)))
,2) + ':'
+ Substring(
stuff(Last_Run_Duration,1,0,replicate('0',6 - len(Last_Run_Duration)))
,3,2) + ':'
+ Right(
stuff(Last_Run_Duration,1,0,replicate('0',6 - len(Last_Run_Duration)))
,2) as Last_Run_Duration
, Substring(cast(Next_Run_Date as char(8)),5,2) + '/'
+ Cast(Right(Next_run_date,2) as char(2)) + '/'
+ Cast(Left(Next_run_date,4) as char(4)) as Next_run_date
, Cast(left(Right(Stuff(Next_run_time,1,0,'0'),6),2) as varchar(2)) + ':'
+ Cast(Left(Right(Next_run_time,4),2) as char(2)) + ':'
+ Cast(right(Next_run_time,2) as char(2)) as Next_run_time
, Schedule_Name
, Schedule_Description
FROM #JobReport
WHERE (@.Freq = '##' OR Schedule_Description like @.Freq)
ORDER BY Job_Name
Drop table #JobReport
Drop table #Jobs
RETURN
END -- END OF PROCEDURE
