stop a jom automatically after a specific time

  • 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

  • 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

  • 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?

  • 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