If SQL agent finds a job running at schedule job start time, agent skips the execution of job until the next scheduled execution time. This can lead to some missing rows. I have tried to demonstrate the same thing below.

Step-1 Create a Job with runs every two minutes and add below statement in job step just to simulate the real life scenario.

waitfor delay '00:01:10'
go

Step-2 Set schedule to run every minute

Leave job for few minutes. After that if we look at the job history, we can see that few executions of jobs are skipped. We have scheduled it to run very minute but its getting run every two minutes.

Step-3 Delete the job.

Apparently this looks like a not a big deal. But if we are doing some data processing like populating the search table or sending data to third party application or may be running SSIS packages and this condition is not taken into consideration, it can be a data loss.

Actually, today I find this as root cause for search not displaying some data in one proprietary system. Sometimes Job was running for more time due to blocking issues. Of course if proper tracking mechanism is used in Procedure, we can avoid the situation, but sometimes developer tends to miss these little things which can cause some issues.

With help of below query, we can check for any skipped execution due to this problem and having time between two consecutive scheduled executions less than one day.

use msdb;
go
with  scheduleCTE
as
(
select 
case freq_type 
     when 4
     then case freq_subday_type  when 1 then  24*60*60 
                                 when 2 then  freq_subday_interval
                                 when 4 then  freq_subday_interval * 60
                                 when 8 then freq_subday_interval * 60 *60
          end
      when 8
      then case freq_subday_type when 1 then 24*60*60 
                                 when 2 then  freq_subday_interval
                                 when 4 then  freq_subday_interval * 60
                                 when 8 then freq_subday_interval * 60 *60
          end
      when 16
      then case freq_subday_type when 1 then -1
                                 when 2 then  freq_subday_interval
                                 when 4 then  freq_subday_interval * 60
                                 when 8 then freq_subday_interval * 60 *60
          end
      end as scheduledDuration
, schedule_id                                                 
from msdb.dbo.sysschedules
)

select sj.name as jobName 
, dateadd(second,CAST((right(Right('000000' + cast(sjh.run_time as nvarchar(10)),6),2)) as int)
        ,dateadd(minute,CAST((substring(Right('000000' + cast(sjh.run_time as nvarchar(10)),6),3,2)) as int) 
        ,dateadd(hour, CAST((left(Right('000000' + cast(sjh.run_time as nvarchar(10)),6),2)) as int) 
        ,cast(CAST (sjh.run_date as varchar(10)) as datetime)))) runDateTime
, sjh.run_duration as runDurationInSeconds
, sc.scheduledDuration scheduledDurationGapInSeconds
from dbo.sysjobhistory sjh
inner join dbo.sysjobs sj on sj.job_id = sjh.job_id
inner join dbo.sysjobschedules sjs on sjs.job_id = sjh.job_id
inner join scheduleCTE sc on sc.schedule_id = sjs.schedule_id
where sjh.step_id = 0 and sjh.run_duration > sc.scheduledDuration
order by jobName;