SQL Agent Time Gap between job steps
-
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
-
I have also noticed there are overlapping SSIS jobs, Is there a max concurrent jobs? for SSIS dtexec.exe?
-
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 -
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) -
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
- richard.alsweiler - Sunday, December 10, 2017 10:49 PM
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? -
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.
-
-
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!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply