5

I have an SQL Server Agent job set up to run every minute on the minute.

Usually it runs exactly at :00 on the clock but occasionally it waits several seconds.

See below, one of the jobs was triggered to run at 6:32AM, but only actually started running 9 seconds later.

enter image description here

I suppose there are many reasons this could happen, but my question is: Is there any way that I can pinpoint what is causing this (as opposed to just guessing, making a change, and hoping that fixes it)?

I read this question and answer, but this is not the issue in my case. I do have other SQL Server Agent jobs, but no other jobs were running at 6:32, and also there are no other jobs that touch any of the tables involved in this particular job. When I tried running the query in the accepted answer of that question, it always resulted in 0 rows of data.

9
  • 3
    To be fair, out of the four sample rows you showed, three of them didn't run on time. :-) Sep 10, 2019 at 11:23
  • The other question has another problem: the duration of job was altered due to blocking, not its start time
    – sepupic
    Sep 10, 2019 at 11:24
  • @sepupic Right, but I wasn't sure if "duration" in that context meant the duration of the step or the duration of the job. Apparently job duration includes delay times but step duration doesn't. I tend to agree with you it is a different problem but I tried it anyway just in case it was related. Sep 10, 2019 at 11:39
  • Does it really matter if it's 9 seconds after the hour? If it needs to be spot on every time maybe you should switch to an enterprise scheduling system? Sep 10, 2019 at 13:29
  • @SeanGallardy Well, I'm working toward trying to increase the frequency of this job from once a minute to once every 30 seconds. Considering the job usually takes about 15-25 seconds to run, having it be possibly delayed by 9 seconds is substantial. Of course, I could use another alternative, just posted the question to see if anyone knew what could cause this problem. Sep 10, 2019 at 14:43

2 Answers 2

4

I would take a look at not just the SQL jobs that are running, but see if Windows (or antivirus) happens to be running something at that same time. I know McAfee has a tendency to run almost at random, and it can really drain my machine's resources.

1
  • Thanks, useful answer, still waiting a bit to see if anyone else has other ideas. But if a few days go by with nothing I will mark as accepted. Sep 11, 2019 at 21:00
0

I have the same issue, but delay is minutes, not seconds like in your case. Just check others job runs made during the same time under same user. In my case job's step just waits till another step of another job finished under the same user. So solutions here:

  1. Enlarge number of simultaneous tasks for the user
  2. Increase number of users
  3. Mix of p.1 and p.2 ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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