Post reply

Job cancelling by itself? (The step was cancelled (stopped) as the result of a stop job request)

  • January 19, 2011 at 1:09 am

    #232332

    Morning all.

    I have a job which executes 4 SSIS packages every night at 17:30.

    Around 7 minutes into the job it automatically cancels itself with the following message in the job history

    Executed at user: DOMAIN\UserName. The step was canceled (stopped) as the result of a stop job request.

    I have done a google on the error and it comes back to someone is stopping the job or executing sp_stop_job, or the agent is restarted around that time.

    The only people who have access to MSDB to stop/start jobs all finish at 17:00 and there are other jobs which run at 17:30 which execute successfully, so the agent isn't restarting.

    Any advice would be appreciated.

    Thanks

    Anthony

  • January 19, 2011 at 4:52 am

    #1275204

    May be worth reviewing this;

    http://support.microsoft.com/kb/922527

    If there's any chance that there was a console log off (compare to the security event logs on the server to see if there is any pattern?) then there is a hotfix available to resolve the issue.

    If there is a pattern and it is stopping 7mins in every night, then it sounds like there may potentially be a problem with the same step or part of the code each time?

    Have you tried skipping the step the failure occurs on also?

  • January 19, 2011 at 5:40 am

    #1275229

    Hi McSQL

    Its usually between 6 and 7 minutes then the job stops.

    One of the articles I was looking at was in relation to a WAITFOR event where it maybe that one of the procedures was still executing and the connection timed out, but I dont think it would be this.

    There is no console logouts as I have checked this as well.

    It isnt just stopping on the schedule, if you manually run the job from step 1 then again around 7 mins later it fails, but sometimes you might get a successful run.

    I have run all of the packages through BIDS this morning and they all run through end to end with no issues.

    There are around 15 packages over 6 jobs all the same structure but its just this one job with these 4 packages which is causing the issue.

    They execute a procedure to truncate 10 tables via an Execute SQL Task, then there is a sequence container to import the same tables it has just truncated, and follows this structure in all 15 packages.

    If it is in relation to the WAITFOR then a change to the packages to execute teh truncates direct and not using the stored procs to truncate.

    Thanks

    Ant

  • January 21, 2011 at 1:28 am

    #1276411

    The job was stopped prior to completion by (Unknown) . The Job was invoked by User SITES\agreen. The last step to run was step 1 (LoadingBayBT1). The job was requested to start at step 1 (LoadingBayBT1).

    I have captured this from profiler while running the job if it helps anyone?

    Thanks

    Ant

  • January 21, 2011 at 3:16 am

    #1276440

    after a bit of playing with the job and bids I have noticed that the first package is the one which is cancelling so it never gets to package 2,3 or 4.

    you can start the job at package 1 and it cancells, but run it via BIDS and it runs fine.

    if you then start the job at package 2 after running package 1 via BIDS the job runs fine.

    to completly rule out the console log off's all of the sessions where disconnected and where monitored via tsadmin.msc on a different server to ensure there where no logins/logouts while running the job and still the job cancelled after 7 minutes.

  • January 21, 2011 at 3:29 am

    #1276443

    A possible suggestion is that the account executing the package via SQL may not have the same permissions as you have yourself when manually executing.

    Maybe check the account which is executing the job and it's permissions on the db (or file/folder/share if cmd) and make sure they're sufficient enough to allow it enough access?

  • March 2, 2011 at 5:38 am

    #1293430

    Similar Problem here - Starting a SSIS package via a job and getting this error: The job was stopped prior to completion by (Unknown). It is an SQL Server 2008 SP1 so this http://support.microsoft.com/kb/922527 doesn't help. The error does not occur always - sometimes the package runs successful and sometimes it stops with the error above.

    Grateful for any Suggestion

  • March 2, 2011 at 5:43 am

    #1293432

    to get around this issue i put event handlers on all of my DFT's and Execute SQL Tasks to log to a table when the task started and finished.

    i managed to get it down to the specific sequence container as it fired all of the DFT's in one go, so put success precedence constraints linking each DFT to the next and finding where it fails

    then i disabled that one DFT and ever since the error has disappered (now that I have said that it will fail I know it)

    the error never happened when running via BIDS locally and was intermittent when running as a job, but now with this DFT gone, its working fine.

  • March 2, 2011 at 5:48 am

    #1293435

    Hi anthony

    thanks for the quick reply.

    but that can't be the solution because the problem is specific (up to now) to this one server maschine only. On my dev system i can run the same Package via a job without any Problem.

Viewing 9 posts - 1 through 8 (of 8 total)

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