9

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?

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Musakkhir Sayyed
  • 490
  • 1
  • 5
  • 18

2 Answers2

13

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
Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
5

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
jyao
  • 3,083
  • 1
  • 14
  • 27