SQL Server Job Scheduling – How to Stop a Job After Condition is Satisfied

jobssql serversql-server-agentt-sql

I have a job that runs every 5 minutes to check an IF condition. If the condition is true, it will send an email, if not, it won’t do anything.

My problem is that once the IF condition is true, it will remain true for the whole day and, as the job is running after every 5 minutes, it will keep sending the email after every 5 minutes.

I need to stop the job for the whole day once the email has been sent once. Is there a way to do so?

Best Answer

I actually wrote a similar tip at mssqltips.com. For your current case, the general idea would be like this:

  1. you may add an additional step at the end of the job, once your primary job steps succeed, the job will run the final step (i.e. the newly added step)

  2. If your job fails in your primary steps, your job will just exit and no need to run the final added step.

  3. In you final job step, you will do one thing, i.e. updating your job schedule' start date to be next day. 

    use msdb

declare @active_date int, @sch_name varchar(128);

select @sch_name=s.name
from msdb.dbo.sysschedules s
inner join msdb.dbo.sysjobschedules js
on js.schedule_id = s.schedule_id
and js.job_id = $(ESCAPE_NONE(JOBID));

set @active_date = cast(replace(cast(cast(getdate()+1 as date) as varchar(10)), '-', '') as int);

exec sp_update_schedule @name=@sch_name, @active_start_date= @active_date;

Of course, I assume your job has one dedicated schedule only. The approach is all self-dependent and you do not need any additional external jobs to manage your current job.

Related Question