SQL Server job schedule information

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)