SQL Agent Recurring Job Schedules in Seconds
The SQL Agent user interface for creating job schedules only allows you to create recurring job schedule as small as 1 minute recurrence intervals.
However this does not mean SQL Agent can not handle recurrence intervals small than this. If you would closely look at the syntax of the stored procedure used to create job schedules, msdb.dbo.sp_add_jobschedule, you will find that the parameter @freq_subday_type has an undocumented (in SQL Server Books Online) value.
SQL Server Books Online states:
[ @freq_subday_type = ] freq_subday_type
Specifies the units for freq_subday_interval. freq_subday_type is int, with a default of 0, and can be one of these values.
Which value is missing in this range of values?
Right, 0x2 is missing, which represents the Seconds unit range
It should say:
Knowing this, creating a job schedule with a recurrence of 10 seconds, becomes as simple as using the following T-SQL script:
-- Add 10 second recurring job schedule
Note: Running jobs in a tight loop, will increase the resources used on you machine. Use this functionality at your own risk!
The following example script shows a complete job script using a recurring job schedule of 10 seconds.
Questions or problems regarding this web site
should be directed to