How can I run a SQL job every 1 second?

Question

I have an SQL Job which processes minimum amount of rows at a given time. Currently its running every 10 seconds which is the minimum available in Job Scheduler. But because of that the table used processed by the Job gets filled with many records. So I need to run the job every 1 second. How can achieve this? please advice.

Answer 1

Create a job that is scheduled to start every minute. Have the job do something like:

WHILE 1=1
BEGIN
    EXEC dbo.SomeProcedure; /*  this would be the 
        name of a stored procedure that does the 
        actual work */
    WAITFOR DELAY '00:00:01.000';
END

Thanks to Max Vernon, who gave me this answer earlier.

Answer 2

E.g.

WHILE 1=1 -- remember infinite loop
Begin 
---- your tsql code goes here
Waitfor delay '00:00:01'
End

Note above does not have any error handling. You can use TRY/CATCH for error handling