5

This question already has an answer here:

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.

marked as duplicate by Kin Shah sql-server Jul 16 '15 at 12:45

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Running something every second sounds like there's something wrong with your approach. Is the job only processing one task per run? Maybe it should handle all that exist in the table – James Z Jul 16 '15 at 16:44
  • This is a nested procedure. The procedure inside can handle records only belonging to a certain item. So the main procedure takes records belonging to a certain item at once, from the table. Not all the records in the table. Issue is there can be records belonging to many items in the table. That's why I have to run it frequently – mayooran Jul 17 '15 at 1:33
6

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.

  • What if you need to stop it? You can refer a column in a table with a bit value while checking the condition for the WHILE loop. If true the loop continues. if false, it ends. – ankit suhail Jul 24 '17 at 13:01
  • on second thoughts. KILL would do the trick here but you need to have required permissions for the same – ankit suhail Jul 24 '17 at 13:03
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

Not the answer you're looking for? Browse other questions tagged or ask your own question.