10 sec. Schedule Recurrence  

Home
Up
What's New
DTS
SQL-DMO
SQL-NS
XP's
Misc. Tools
Events
FAQ
Links
About...

 

SQL Agent Recurring Job Schedules in Seconds

version: 1.0.0.0

last updated: 6 July 2003

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.

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

 It should say:

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

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.

Download 10secjob.sql

***

Questions or problems regarding this web site should be directed to webmaster@sqldev.net.
Copyright © 1991-2003 SQLDev.Net. All rights reserved.
Last modified: 07/06/03.