16 January,2012 by Jack Vamvas
1) How can I execute a SQL Server Agent Job every few seconds? Administrators try to use SQL Server Agent as a real-time scheduler - and will attempt to exploit the in- build scheduler functionality.
2) I wouldn’t recommend SQL Server Agent for sub-minute frequency scheduling. There are better suited tools for this task. But , if SQL Server Agent is the only available option, use the stored procedures:
The @freq_subday_type is associated with msdb..sysjobschedules .
The msdb..sysjobschedules table allows the extra value of 0x2 (seconds).This example script updates a schedule to run every 15 seconds
GO EXEC msdb.dbo.sp_update_schedule @schedule_id=4, @freq_subday_interval=15, @freq_subday_type=2 GO