How to schedule a job using sp_add_job to run every 10 seconds indefinitely?

Question

How do I schedule a job using sp_add_job to run every 10 seconds indefinitely? I am not sure if I am using the right parameters on the sp_add_jobschedule.

Below is the T-SQL code I am using.

BEGIN TRY
BEGIN TRAN

DECLARE @jobId BINARY(16)

--Add job
EXEC msdb.dbo.sp_add_job @job_name=N'Update TimeStamp in table', @job_id = @jobId OUTPUT

--Add step to job
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Do SQL Stuff', 
        @step_id=1, 
        @subsystem=N'TSQL', 
        @command=N'Exec StoredProc', 
        @database_name=N'DB', 
        @flags=0

--Add schedule to job
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'every minute', 
        @freq_type=4, --
        @freq_interval=64, 
        @freq_subday_type=0x2, 
        @freq_subday_interval=10, 
        @freq_relative_interval=0,
        @active_start_date=20150403, 
        @active_end_date=99991231

COMMIT TRAN
END TRY

BEGIN CATCH
    SELECT ERROR_Message(), ERROR_Line();
    ROLLBACK TRAN
END CATCH

a stored procedure needs to be executed every 10 seconds. SQL Agent Job — user2748147, Dec 04 '15 at 02:29
Thanks for the good example with a lot of parameters filled! Already upvoted (as corner cases are good to know), but it would be nice to have magic numbers commented. — Dmitry Karpenko, Nov 19 '20 at 15:31

score 1 · Answer 1 · answered Mar 17 '22 at 16:10

This one. Tested it, it's working

USE [msdb];
GO

EXEC dbo.sp_add_job  
    @job_name = N'job name',
    @category_name=N'[Uncategorized (Local)]',
    @owner_login_name=N'sa',
    @notify_level_eventlog=0 ;
GO 

EXEC sp_add_jobstep  
    @job_name = N'job name',  
    @step_id = 1, 
    @step_name = N'step name',  
    @subsystem = N'TSQL',  
    @command = N'

-- actual query
insert into dbo.Test1 (number, string, some_date)
values (rand(), ''text'', GETDATE())

    ',   
    @retry_attempts = 0,  
    @retry_interval = 0,
    @on_success_action = 1,
    @database_name=N'OddsAndEnds' ;
GO  

EXEC dbo.sp_add_jobserver  
    @job_name = N'job name',
    @server_name = N'(local)' ;
GO 

EXEC sp_add_jobschedule 
    @job_name = N'job name',
    @name = N'test job schedule', 
    @enabled = 1,   --enabled
    @freq_type = 4, -- on daily basis
    @freq_interval = 1, -- don't use this one
    @freq_subday_type = 2,  -- units between each exec: seconds
    @freq_subday_interval = 10,  -- number of units between each exec
    @active_start_date=20190312, 
    @active_end_date=99991231, 
    @schedule_uid=N'8912aa53-ffe9-4f31-b6cb-9a8e2f1ee6e3'

score 0 · Answer 2 · answered Mar 12 '18 at 16:37

Check the documentation here. It discourages to run a job every 10 seconds:

freq_subday_intervalis int, with a default of 0. Note: Interval should be longer than 10 seconds. freq_subday_interval is ignored in those cases where freq_subday_type is equal to 1.

How to schedule a job using sp_add_job to run every 10 seconds indefinitely?

2 Answers2