Is there a way to set a SQL Server job scheduled to run every 30 seconds?

Question

When I try to create a Schedule the minimum amount of time I can choose from is 1 minute, is there a way to reduce this to seconds?

Solution 1

This article here SQL Server Job Scheduling says you can - just not directly from the UI (the seconds aren't exposed as a valid choice).

See in about the middle of the page:

Schedules for frequent executing jobs

SQL server jobs can have high running frequency with interval less than 1 minute. But this capability is not exposed to SQL agent GUI, only “Hours” and “Minutes” are supported.

This can be achieved by calling the stored procedure
msdb.dbo.sp_add_jobschedule or
msdb.dbo.sp_update_jobschedule.

The stored procedures have a parameter @freq_subday_type, it has three values according to BOL:

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

For the same column in msdb..sysjobschedules table, it has four values, which includes 0x2 for seconds.

Though 0x2 is not documented for the two stored procedures, it can accept the value and create the schedule correctly, e.g. this script will create a job runs every 30 seconds everyday.

Solution 2

yes, I had the same problem resolved with 2 different schedules! same step (1 minute), but different start time. Hope this will help.