SQL Agent Time Gap between job steps

Question

Post reply

SQL Agent Time Gap between job steps

TangoVictor

SSChampion

Points: 10975
More actions
August 21, 2009 at 8:21 am

#209803

last night I got paged because a job ran about twice as long as it should so I checked the job steps and each step was within the normal run times but the job as a whole was way off.
Does anyone have any idea?
Here is a sample of the job steps right from the log: (step 0 is the summary which shows run time over 46 minutes but the sum of the steps is around 19 minutes)
You can see step 1 launched at 3:52 and ran for 2:09. Step 2 didn't launch until 4:22, over 25 minutes later.
8/21/2009 3:52:0000:46:32
8/21/2009 4:38:32110:00:00
8/21/2009 4:38:32100:00:00
8/21/2009 4:38:1590:00:17
8/21/2009 4:37:3480:00:41
8/21/2009 4:37:1170:00:23
8/21/2009 4:25:5560:11:16
8/21/2009 4:25:3050:00:25
8/21/2009 4:25:1040:00:20
8/21/2009 4:22:4030:02:30
8/21/2009 4:22:1320:00:26
8/21/2009 3:52:0010:02:09

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

TangoVictor SSChampion Points: 10975 More actions · Answer 1

I have also noticed there are overlapping SSIS jobs, Is there a max concurrent jobs? for SSIS dtexec.exe?

richard.alsweiler SSC Rookie Points: 43 More actions · Answer 2

Hi Tom,

I know it has been 8 years - but we are experiencing the exact same issue - where there is a gap between job steps, the second step is an SSIS package.

I don't suppose you remember any resolution to this?

thanks in advance,
Richard

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Could it be simple blocking of table resources since more than one job is running?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Might also need to check if there are retry attempts and what the interval between retries is set to.
If a job has multiple schedules with conflicts that can also lead to some odd behavior.

Sue

TangoVictor SSChampion Points: 10975 More actions · Answer 5

richard.alsweiler - Sunday, December 10, 2017 10:49 PM
Hi Tom,
I know it has been 8 years - but we are experiencing the exact same issue - where there is a gap between job steps, the second step is an SSIS package.
I don't suppose you remember any resolution to this?
thanks in advance,
Richard

Hey Richard,
Jeez, you know i don't even recall making this post. Clearly it wasn't a recurring issue and I would only be speculating as to the cause. The first thing that comes to mind is an issue we had with a Pervasive(Actian) connection that would hang sometimes for hours. But in those cases the job would be manually killed and the job history would never show completion so that really doesn't match this case. In fact any job related blocking wouldn't impact this since the job steps are complete, although blocking with msdb may be a cause, or with agent logs, something like that.
I really think this was an isolated incident. Are you seeing a recurring issue? Can you reproduce it?

richard.alsweiler SSC Rookie Points: 43 More actions · Answer 6

Great ideas, I will investigate sql agent blocking and report back. For reference you can see the issue below. Step one finished at 13:00:30 but step two does not begin until 13:02:36. We are seeing this in different sql jobs intermittently but at least once a day.

richard.alsweiler SSC Rookie Points: 43 More actions · Answer 7

richard.alsweiler

SSC Rookie

Points: 43

December 11, 2017 at 4:15 pm

#1971683

SoCal_DBD Hall of Fame Points: 3383 More actions · Answer 8

Old post... but wanted to add my screenshot with similar behavior. Job has 2 steps. Each were 1 second in duration, but Step 2 started 20 seconds after Step 1. In my case, both steps are simply SQL statements. DBA not sure either!

JobDuration