Conditional SQL Ajent Job execution

I have a job set to execute every 3 minutes. Only one instance of the
job should be running at any given time. If execution time exceeds 3
minutes - job should not start. Currently I have a flag in a
user-defined table that is set to 1 by the SP that is called by the
job when it begins, and reset back to 0 when it ends. The SP checks
the value of the flag, if it is currently = 1 then the SP exits.
The problem is that if the SP fails half-way through the flag is never
reset back to 0, thus preventing subsequent executions.
Is there a way to set the job to execute conditionally based on
whether or not another instance of same job is already running?I will probably add check the sysjobhistory to verify that
the job didn't fail:
select top 2 *
from sysjobhistory
where job_id = (select job_id from sysjobs
where name = <'Job Name'>)
order by run_date, convert(smalldatetime, (case len
(run_time)
when 1 then '00'+':'+'00'+':'+'0'+ right
(run_time,2)
when 2 then '00'+':'+'00'+':'+right
(run_time,2)
when 3 then '00'+':'+'0'+left(convert(char
(3), run_time), 1)+':'+right(run_time,2)
when 4 then '00'+':'+left(convert(char(4),
run_time), 2)+':'+right(run_time,2)
when 5 then '0'+left(convert(char(5),
run_time), 1)+':'+substring(convert(char(5), run_time), 2,
2)+':'+right(run_time,2)
when 6 then left(convert(char(6),
run_time), 2)+':'+substring(convert(char(6), run_time), 3,
2)+':'+right(run_time,2)
END)) desc
If the job failed, it should continue with the schedule:
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I have a job set to execute every 3 minutes. Only one
instance of the
>job should be running at any given time. If execution
time exceeds 3
>minutes - job should not start. Currently I have a flag
in a
>user-defined table that is set to 1 by the SP that is
called by the
>job when it begins, and reset back to 0 when it ends.
The SP checks
>the value of the flag, if it is currently = 1 then the SP
exits.
>The problem is that if the SP fails half-way through the
flag is never
>reset back to 0, thus preventing subsequent executions.
>Is there a way to set the job to execute conditionally
based on
>whether or not another instance of same job is already
running?
>.
>|||You shouldn't need such flags etc. Agent will not start a job if it is still executing (from last
time).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alexey Aksyonenko" <Alexey.Aksyonenko@.coanetwork.com> wrote in message
news:1449e414.0309300914.2af4241@.posting.google.com...
> I have a job set to execute every 3 minutes. Only one instance of the
> job should be running at any given time. If execution time exceeds 3
> minutes - job should not start. Currently I have a flag in a
> user-defined table that is set to 1 by the SP that is called by the
> job when it begins, and reset back to 0 when it ends. The SP checks
> the value of the flag, if it is currently = 1 then the SP exits.
> The problem is that if the SP fails half-way through the flag is never
> reset back to 0, thus preventing subsequent executions.
> Is there a way to set the job to execute conditionally based on
> whether or not another instance of same job is already running?