May 18, 2016 at 12:46 am
Good morning
I have a SSIS package that runs through a job and it takes about 1—14 hours to complete
The problem is that if the job hasn’t finished on working hours I have a lot of problems with other systems
Is there any way ( a job or stored procedure or SQL commands) that when a specific job exceeds a specific time , the job will be stopped immediately
thanks
May 20, 2016 at 9:09 am
I expect there are a few ways to do this, but you can query the msdb.dbo.sysjobactivity table to monitor the progress of a job. If the stop_execution_date is absent, it's still running:
SELECT TOP 1 SJ.name,
SJA.run_requested_date,
SJA.start_execution_date,
SJA.last_executed_step_date,
SJA.stop_execution_date
FROM msdb.dbo.sysjobactivity SJA
INNER JOIN msdb.dbo.sysjobs SJ ON SJ.job_id = SJA.job_id
WHERE SJ.name = 'InsertJobNameHere'
ORDER BY SJA.start_execution_date DESC
Using DATEDIFF on the start and stop execution date values:
https://msdn.microsoft.com/en-GB/library/ms189794.aspx
You should be able to apply some logic to execute a stop command:
May 21, 2016 at 7:31 pm
The idea given above from BaronGreen.bak is what I'd do. Have a job query this one for time, and then stop it if it's taken too long.
Be careful and be sure you understand what happens if you stop the SSIS job. Is a partial load occurring? Can you restart?
May 23, 2016 at 10:39 pm
The step above would also be the approach I take. As mentioned by other users ensure that if it does stop it has no impact to anything within the system and can be re-runnable at a later time with no issues.
As you said in your comment its anywhere from 1 to 14 hours... that's a very big difference time wise. Without knowing why its 1 to 14 hours. I would try to get to the root cause why it takes so long and if it can be optimised.
If it can't be optimised and you have no choice , hopefully your job is in steps so you can restart at the step it failed instead of repeating the whole job from scratch.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply