Re: Limitations of Scheduled jobs

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 03/15/04 

Date: Mon, 15 Mar 2004 10:54:06 -0000

Well, you can set @freq_subday_type to seconds (0x2), but it is not
documented in Books Online, which means that it is not supported by
Microsoft, so if anything goes wrong, you are on your own.

There is not going to be an overlap, because a job can not be started while
it is still running. You can't run a job with a certain interval if the job
takes longer to run than that interval, or at least the job would only start
at a designated starttime of it is not running already. 

-- 
Jacco Schalkwijk
SQL Server MVP
<anonymous@discussions.microsoft.com> wrote in message
news:a46e01c40a77$2beec1f0$a601280a@phx.gbl...
> Hi,
> Thanks for the response.
> Actually we can schedule the jobs with accuracy less than
> 1 min. Enterprise manager GUI allows only minutes but
> using SQL script, we can set the interval parameter to any
> seconds(using @freq_subday_interval & @freq_subday_type),
> this way you create only one job. I was able to do that.
> What I just found from my testing is even if I set the
> duration to 3 seconds and the SP is taking longer than
> that, there would be no overlap since the next iteration
> of the job would fire only when the first is completed.
> Pls. correct me if I'm wrong.
>
> >-----Original Message-----
> >Hi Nonnie,
> >
> >That is not going to work with a job, because you can
> only schedule jobs
> >with an accuracy of 1 minute, and you can't have 2
> instances of the same job
> >running at the same time. You can work around these
> limitations if you have
> >2 jobs (or however many you need to cover an interval
> that is longer than
> >the job duration), use an endless loop in a job, and use
> the WAITFOR TIME
> >statement to time kicking of the appropriate job.
> Sp_start_job works
> >asynchronously, where executing the stored procedure
> works synchronously, so
> >that wouldn't solve the problem. For example:
> >
> >DECLARE @i INT
> >
> >SET @i = 0
> >WHILE 1=1
> >BEGIN
> >    IF @i % 2 = 0
> >        EXEC sp_start_job job_1
> >    IF @i % 2 = 1
> >        EXEC sp_start_job job_1
> >    SET @i = @i + 1
> >    WAITFOR DELAY '00:00:03'
> >END
> >
> >It might not be a good idea btw to run the stored
> procedure when it is still
> >running, as it might be blocked by the previous instance,
> but that depends
> >on what the stored procedure does exactly.
> >
> >-- 
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Nonnie" <nonniem@rediffmail.com> wrote in message
> >news:cfb501c40a63$e34a8b90$a301280a@phx.gbl...
> >> Hi,
> >> I'm using SQL Server 2000 as the DB for my web
> application
> >> and I have a stored procedure that needs to be run
> every 3
> >> seconds. But the execution of this SP takes more than 3
> >> sec. Is it okay to schedule a job for this or is it
> better
> >> to write a .NET windows service and use multi threading?
> >> Please let me know the limitations of both the choices.
> >
> >
> >.
> >

Relevant Pages