September 14, 2013 at 7:39 am
How to get job schedule details, I mean detailed schedule like, all the jobs on all 7 days.
Sun Mon Tue
XXXXXX job - runs at 9 am no run runs at 9 am
so what i require is list lal the jobs and its run/not run schedule for every 7 days. Does anyone have the query?
September 15, 2013 at 3:18 am
Hi,
Why not just see it in the GUI? 😮 This will do the trick anyways:
with t1 as (SELECT @@SERVERNAME server, sj.[name] name,
msdb.dbo.agent_datetime(run_date,run_time) start_time,
run_date run_date,
run_time run_time,
replicate('0', 6-len(run_duration)) + cast (run_duration as char(6)) dur_pad,
sh.run_status status,
case when run_status=0 then substring(sh.message,0,4000)
else null
end as error_message
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory sh
ON sh.job_id = sj.job_id
INNER JOIN msdb.dbo.sysjobsteps ss
ON sj.job_id = ss.job_id
AND sh.step_id = ss.step_id
where msdb.dbo.agent_datetime(run_date,run_time) > getdate()-7)
select server,name,start_time,
DATEADD(SECOND, CONVERT(int, SUBSTRING(dur_pad, 5, 2)),
DATEADD(MINUTE, CONVERT(int, SUBSTRING(dur_pad, 3, 2)),
DATEADD(HOUR, CONVERT(int, SUBSTRING(dur_pad, 1, 2)),
msdb.dbo.agent_datetime(run_date,run_time)))) end_time,
case when status = 0 then 'F' else 'C' end,error_message
from t1;
September 15, 2013 at 12:33 pm
The query given is useful to me in a different way. But its not what i desired. What i need is whether a job is scheduled to run on Sunday,monday,tuesday...Saturday and if its scheduled then the scheduled time to run (if no scheudle on a particular day, then no value/blank/Not applicable). So, the result would have the list of all the jobs and its schedule (or not scheduled to run) on each day in a week.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply