When we schedule a SQL server agent job, all schedule information is stored on the system table sysschedules table in MSDB.
With the help of below sql script we will get the all details about the job.
|
select DISTINCT
sj.name As [job name],sysc.name as [job category],
sjc.step_name as [Step name],sjc.subsystem,
sc.name as [schedule name],
sc.enabled,sc.freq_type,
sc.freq_interval,sc.freq_subday_interval,
sc.active_start_date,sc.active_end_date,
sc.date_created,sc.date_modified
from sysjobs sj
inner join syscategories sysc
ON sysc.category_id=sj.category_id
LEFT OUTER JOIN sysjobsteps sjs
on sjs.job_id=sj.job_id
LEFT OUTER JOIN sysjobsteps sjc
ON SJ.job_id = sjc.job_id
LEFT OUTER JOIN sysschedules sc
on sc.schedule_id=sjc.step_id
|
We will get below information
Enabled column
This column store the information either schedule is enabled or not of the value is 1 it means the schedule is active and if it is zero then this schedule is disabled.
Freq_Type Columns
How frequently a job runs for this schedule.
· 1 = One time only
· 4 = Daily
· 8 = Weekly
· 16 = Monthly
· 32 = Monthly, relative to freq_interval
· 64 = Runs when the SQL Server Agent service starts
· 128 = Runs when the computer is idle
Freq_subday_type Column
Units for the freq_subday_interval. The following are the possible values and their descriptions.
· 1 : At the specified time
· 2 : Seconds
· 4 : Minutes
· 8 : Hours
Freq_relative_interval Column
When freq_interval occurs in each month if freq_interval is 32 (monthly relative). Can be one of the following values:
· 0 = Freq_relative_interval is unused
· 1 = First
· 2 = Second
· 4 = Third
· 8 = Fourth
· 16 = Last
Freq_Type and freq_interval in details
|
Value of Freq_Type
|
Effect on freq_interval
|
|
1 (once)
|
freq_interval is unused (0)
|
|
4 (daily)
|
Every freq_interval days
|
|
8 (weekly)
|
freq_interval is one or more of the following:
|
|
1 = Sunday
|
|
|
2 = Monday
|
|
|
4 = Tuesday
|
|
|
8 = Wednesday
|
|
|
16 = Thursday
|
|
|
32 = Friday
|
|
|
64 = Saturday
|
|
|
16 (monthly)
|
On the freq_interval day of the month
|
|
32 (monthly, relative)
|
freq_interval is one of the following:
|
|
1 = Sunday
|
|
|
2 = Monday
|
|
|
3 = Tuesday
|
|
|
4 = Wednesday
|
|
|
5 = Thursday
|
|
|
6 = Friday
|
|
|
7 = Saturday
|
|
|
8 = Day
|
|
|
9 = Weekday
|
|
|
10 = Weekend day
|
|
|
64 (starts when SQL Server Agent service starts)
|
freq_interval is unused (0)
|
|
128 (runs when the computer is idle)
|
freq_interval is unused (0)
|
