3

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?

  • Can we have sample query for how you check True condition? – MarmiK Feb 14 at 11:50
2

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.

  • I used this but the job didn’t started next day. Any help would be appreciated. – Hitesh Khandelwal Feb 16 at 5:00
  • can you check the job schedule and see the active start date of the schedule, also whether the schedule is enabled? Also please tell me your sql server version and I can try to see whether this can be repeated. – jyao Feb 16 at 5:58
  • In my sql server 2016 Developer Edition test environment, I can prove that my solution works as expected. – jyao Feb 16 at 19:42
  • Sorry for late reply but I was able to run it. Thank you for this. After checking the whole process I got to know that this job should not run on sat and sun. So looking for some solution for that now. But thank you for helping me out here. :) – Hitesh Khandelwal Feb 21 at 9:57
  • Thanks @HiteshKhandelwal for the feedback. It is actually a good topic, i.e. once the job succeeds, I want the job to run on a scheduled time of the next scheduled date. So if a job is scheduled to run from Mon to Fri only (no Sat/Sun), on Friday, if it succeeds, the active_date should be next Monday instead of the upcoming Saturday. I do not have any concrete code out of my mind right now, but I believe it is doable, and will explore this topic and update here. Thx again for raising up these interesting requirements. – jyao Feb 21 at 17:27
1

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
  • A newbie question:@schedule_id is the id of my job or it should remain 51 only? – Hitesh Khandelwal Feb 13 at 20:48
  • Go to the schedules tab of the job and you'll see the IDs in the first column. – Tony Hinkle Feb 13 at 20:52
1

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.

0

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
0

If it's an option, instead of running the job periodically throughout the day, you might consider kicking the job off once and putting the checking for the IF condition in a WHILE loop.

So, let's say the job kicks off once daily at 12am. The following TSQL script example would be placed inside the job step and the job would not actually complete until the IF condition has been satisfied.

My example is checking to see if the current time is greater than 4:00pm. This is just for demo purposes. The actual code would be to check for whatever satisfies the IF condition. If the condition is not satisfied, we simply WAITFOR DELAY and continue in the WHILE loop. My example checks for the IF condition every 10 seconds, but you can put whatever delay you want.

You could even include checks (in the WHILE loop) to see if the job has run past a specific time. In that case, you could send a warning email indicating early shutdown and exit the WHILE loop ending today's run of the job.

DECLARE @IfConditionSatisfied BIT = 0

WHILE @IfConditionSatisfied = 0
BEGIN
    IF convert(TIME(0), GETDATE()) > '16:00:00' --simulate the criteria that satisfied the IF condition
    BEGIN
        SET @IfConditionSatisfied = 1
        --send emails, etc.
        --exit job
    END
    ELSE
    BEGIN
        WAITFOR DELAY '00:00:10' --wait for 10 seconds (or specify other hours, minutes, seconds)
    END
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.