Share via

SQL Agent Job - Set TimeOut and Force Failure to Notify

Ismail Cassiem 226 Reputation points
2021-01-15T22:04:14.427+00:00

Hi,

I moved a problematic ssis pkg that did not fail and ran for hours, normally 10min to its own Job

How can i force the job to end/fail when limit has expired?

Regards

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,364 questions
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2021-01-15T22:32:21.197+00:00

    You should probably extend your post to include the sql-server- integration-services tag, since the SSIS people may know if SSIS has any support for this.

    I don't know SSIS, so I can only answer from a general Agent perspective. Agent has no support for this itself. You would have to set up a job that monitors the SSIS job step and kills it if has been running to long. This job could be started from an initial job step of the SSIS job. That job would wait for ten minutes and then kill the other job. Alternatively, you could schedule a job that runs every ten minutes or so and checks for the other job.

    And, no, I don't have the full details to do this, since I don't work with SSIS.

    But as you can see it is a bit of a kludge, and I am not sure that it is a good idea in the first place. What would be the benefit to kill the job?

    2 people found this answer helpful.
    0 comments
  2. Dirk Hondong 871 Reputation points
    2021-01-16T14:29:18.703+00:00

    Hi Ismail,

    in that case I'd go for the suggestion by Erland. Start a separate Job/Process which either kills your SSIS Job after 10 Minutes or "just" monitors the SSIS Job.
    If the job is still active and has a runtime > 10 mins, then kill the job and maybe restart it after 30 seconds.

    But this is just a workaround. It is more important to figure out why the SSIS package stucks and cannot complete its task.

    Regards
    Dirk

    2 people found this answer helpful.
  3. Ismail Cassiem 226 Reputation points
    2021-01-15T22:57:37.207+00:00

    Hi,
    Thank you so much for assisting, i have an initial post on ssis forum where it was to fail the package but no success hence why i started the ques here and moved to its own job. i have expanded on the ssis with the same ques.

    The reason being, is that when it does not fail it runs for hours and affects other jobs depneding on the data of the job. i cant stop and rerun later as im not aware of the issue/no alert for no failure

    i have set the job to run twice in case it fails but if it hangs i can only stop it the next day manually

    regards

    0 comments
  4. Ismail Cassiem 226 Reputation points
    2021-01-16T17:00:51.4+00:00

    Hi,
    Thank You both, the pkg lookp and connects to oracle db's and it either tries to reconnect when unable to connect via c# script ot lost connection it just hangs.
    Regards

    0 comments
  5. Ismail Cassiem 226 Reputation points
    2021-01-16T17:01:52.577+00:00

    I will try and find an example of checking job then failing after10min

    0 comments