Friday, March 23, 2012

job schedule information

Hi,
Does anyone has a script to find out what are the scheduled timining for all sql server agent jobs.
I'm trying to get the result with the following query, it doen't seem working..

select sj.name,
case sjs.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end as Frequency,
case sjs.freq_interval
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 as DAY
from sysjobs sj, sysjobschedules sjs
where sj.job_id = sjs.job_id

Any help would be greately appreciated.
ThanksThat should work fine. Just remember to run that against the msdb database!

Or you could just do...

select sj.name,
case sjs.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end as Frequency,
case sjs.freq_interval
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 as DAY
from msdb..sysjobs sj, msdb..sysjobschedules sjs
where sj.job_id = sjs.job_id|||It not working the way i wanted. I wanted to get something like..
if the job runs every 10 min between 6 am to 6 pm.. i need to get that info also.
Any suggessions.

No comments:

Post a Comment