8

I want to run a job every 3 seconds, however in SQL Server 2008 we cannot define an interval of less than 10 seconds.

The job is used to insert/update visitor information, and segmentation information into a database which is tracked by google search.

There are up to about 100 rows inserted in a 2 or 3 seconds. That job inserts and update the table in a database. Is there any way to schedule it using sp job scheduling configuration?

12

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:03.000';
END
  • Wont this just spawn the same infinitely-looping job every minute until the end of time? – Brandon Nov 26 '15 at 21:06
  • No, any given job can only ever have a single instance running at any given time. – Max Vernon Nov 26 '15 at 22:29
  • If the job fails or is stopped, it will restart in approximately 1 minute. – Max Vernon Nov 26 '15 at 22:30
  • 1
    Ah okay. I was wondering if that was the case but I didn't have a chance to try it. Carry on, nothing to see here. – Brandon Nov 27 '15 at 0:43
  • 1
    worth noting that you need to tell the loop to quit if you want the jobstep to write success or failure to the log. – Peter Vandivier Feb 23 '16 at 20:06
4

I do not think this answer is correct. The reason is as follows: let's say dbo.SomeProcedure will run for 2 seconds and it starts at 10:00:00am, then after this proc finishes, it will wait for another 3 seconds before restart again, i.e. at 10:00:02am, it finishes, and it will not start until 10:00:05am. While if we could really schedule a job to run every 3 seconds, dbo.SomeProcedure will indeed run at 10:00:00am and 10:00:03am, and so on so forth. The more accurate one should be the following:

    WHILE 1=1
    BEGIN
      EXEC dbo.SomeProcedure; /*  this would be the 
      name of a stored procedure that does the actual work */

      WHILE datediff(second, @dt, getdate())%3 <> 0
        WAITFOR DELAY '00:00:00.100'; -- can be made it to '00:00:00.001'
   END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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