SQL Agent Recurring Jobs 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.

[ @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.

Value	Description (unit)
0x1	At the specified time
0x4	Minutes
0x8	Hours

Which value is missing in this range of values?

Right, 0x2 is missing, which represents the Seconds unit range

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
declare @rc = int,
        @JobID uniqueidentifier

select @JobID = <ToDo: You have to fill in your JobID here>

exec @rc = msdb.dbo.sp_add_jobschedule
    @job_id = @JobID,                  -- ID of job you create the schedule for
    @name = N'10 sec recurring',       -- name of the schedule
    @enabled = 1,                      -- enable the schedule for use
    @freq_type = 4,                    -- daily frequency
    @active_start_date = 20030706,     -- date from which schedule will be active (July 6, 2003)
    @active_start_time = 0,            -- time from which schedule will be active (midnight)
    @freq_interval = 1,                -- unused for daily
    @freq_subday_type = 2,             -- seconds
    @freq_subday_interval = 10,        -- every 10 seconds
    @freq_relative_interval = 0,       -- unused for daily
    @freq_recurrence_factor = 0,       -- unused for daily
    @active_end_date = 99991231,       -- date which schedule becomes inactive (Dec. 31, 9999)
    @active_end_time = 235959          -- time which schedule becomes inactive (23:59:59)
if (@@error <> 0 or @rc <> 0) raiserror('Error creating job schedule', 1, 16)

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.

SQL Agent Recurring Job Schedules in Seconds