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.
2
-
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 ZCommented Jul 16, 2015 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– AnOldSoulCommented Jul 17, 2015 at 1:33
Add a comment
|
2 Answers
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.
answered Jul 16, 2015 at 12:40
-
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. Commented Jul 24, 2017 at 13:01
-
on second thoughts. KILL would do the trick here but you need to have required permissions for the same Commented Jul 24, 2017 at 13:03
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
answered Jul 16, 2015 at 12:41
