stop a jom automatically after a specific time

Question

stop a jom automatically after a specific time

gxdatareal

Ten Centuries

Points: 1022
More actions
May 18, 2016 at 12:46 am

#318896

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

Viewing 4 posts - 1 through 3 (of 3 total)

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

simmonssql Old Hand Points: 301 More actions · Answer 1

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:

https://msdn.microsoft.com/en-us/library/ms182793.aspx

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

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?

Tava SSCarpal Tunnel Points: 4122 More actions · Answer 3

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.