Share via

Update SQL Agent Jobs Schedules with T-SQL

  • Article

after building your SSIS project and putting it into action you just start doing some JOB Schedules through the SQL Agent, but when you have a lot of schedules updating them starts to get annoying. and you can start getting some queries to list the Jobs, tasks and schedules in our discussion now we’ll focus on table “sysschedules” as this is the one that holds the info for schedules. and we’ll focus on exactly two fields.

freq_type 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

and

active_start_time: Time on any day between active_start_date and active_end_date that job begins executing. Time is formatted HHMMSS, using a 24-hour clock.

now let’s say that all your schedules are daily and they are in frequent starting 1am. then you need to add a new schedule before them but you don’t need to re-schedule all of the jobs using the SQL Agent, simply update the table adding 3000 (30 minutes) to all schedules so will be alerted.

you can as well change the frequency, active status through this table. the detailed table documentation can be found here

Comments

  • Anonymous
    January 01, 2003
    Good stuff..