Ytdyklly

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?

Look at the job schedule to get the ID of the schedule--I'll use schedule ID 51 in the following examples. You can disable the schedule on the job when the condition is first found to be true:

USE [msdb]

GO

EXEC msdb.dbo.sp_update_schedule @schedule_id=51, 

    @enabled=0

GO

Then set up another job to run at the beginning of the day (or whenever the condition will be false again) to enable it:

USE [msdb]

GO

EXEC msdb.dbo.sp_update_schedule @schedule_id=51, 

    @enabled=1

GO

One thing to note--a schedule can be used on multiple jobs, so you just need to ensure that schedule is not also being used for other jobs. So to check if schedule ID 51 is being used by multiple jobs:

USE msdb

GO

SELECT * FROM sysjobschedules WHERE schedule_id = 51

Another option would be to create a small table somewhere that has a single datetime flag column. When your condition evaluates to TRUE then you update that value to the current datetime.

You will also need to add the condition to your IF statement that CAST(DatetimeFLAG AS DATE) must be < CAST(GETDATE() AS DATE). This basically stops it from evaluating to TRUE again on the same date.

This option will work if you don't want to mess with schedules.

If your job executes a stored procedure, you can check the job history at the beginning of the SP and return if the job has been successfully executed today.

IF EXISTS(SELECT 1

          FROM

            msdb.dbo.sysjobs as job

          JOIN

            msdb.dbo.sysjobhistory his

            ON job.job_id = his.job_id

          WHERE

            job.name = 'your job name'

            AND CAST(CAST(his.run_date AS char(8)) as date) = CAST(GETDATE() as date)

            AND his.run_status = 1)

BEGIN

  RETURN;

END

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.