Is there a way to set up a SQL Server job that should run every 30 seconds?

Question

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

Answer 1

This SQL Server job scheduling article here says that you can - just not directly from the user interface (seconds are not displayed as a valid choice).

See approximately in the middle of the page:

Schedules for frequently performed tasks

SQL Server jobs can have a high level of performance frequency at intervals of less than 1 minute. But this feature is not exposed to the graphical interface of the SQL agent, only “Hours” and “Minutes”.

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

Stored procedures have the @freq_subday_type parameter, it has three values ​​according to BOL:

Value Description (unit)
0x1 At the specified time.
0x4 minutes.
0x8 hours.

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

Although 0x2 is not documented for two stored procedures, it can take a value and create a schedule correctly, for example. this script will create a task every 30 seconds every day.

Answer 2

Yes, you can create a DTS task for this.

http://msdn.microsoft.com/en-us/library/cc917688.aspx

http://technet.microsoft.com/en-us/library/cc917688.aspx

Answer 3

Yes, I had a problem with two different schedules! same step (1 minute), but a different start time. Hope this helps.