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?

Artois

What on earth do you need to run twice a minute? – Dunton

aye, its not my choice, its the pointy haired one's... we need to send some push notifications and they want them asap...specially because local pointy hair is having a meeting with the bigger pointy hairs soon, ands want to show the demo working FAST (not having to wait a minute for the process to kick in...we'll probably just add a button for the demo though..) – Artois

Haha I wouldn't recommend that! – Fetishist

If new data is not generated this often, you could consider starting the job with a trigger. Which has it's own nasitness involved (the trigger operates inside the transaction), but might be preferable in your buisness case. Another option: let the application that is doing the database modification also start the job – Wite

Yes we are probably going to use the last option you mention, but for the mean while marc_s'answer is pretty solid. :) – Artois

Answer 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.

Underbrush

Answer 2

yes, you can create a DTS job for that.

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

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

Gilbertegilbertian