-2

I have an ETL process that runs nightly (that I inherited). About 1 out of 5 times the job will fail and have a failed status in the Job Activity monitor. No other discernable message there. I enabled logging to a file to get more info and the only message i get in that log is: Msg 0, Sev 0, State 1: Operation canceled [SQLSTATE HY008]

Looking that error message up isn't particularly helpful as basically all it says is that something requested the job to cancel and it did. There are no errors in the sql server log, nor in the sql agent logs, I also looked in the windows logs to see if I could find an O/S level event that might have triggered a cancel request. nothing. If I re-run the job (it runs from scratch when doing so) it will run successfully. Also it doesn't fail at the same point, or even in the same stored procedure (there are a series of stored procs that get called). I have a ton of logging going on so I know pretty much in what section things fail and typically it's a sql statement that is nothing special. A select ... into sorta statement. How do I get more information so I can know why this is happening as this is maddening.

4
  • How does the Job start?...is it setup with a schedule via the SQL Agent or do you have another process that manually starts the job?
    – J.D.
    Oct 10 at 2:14
  • Does it always get cancelled at the same time of day? Is there a second job that is scheduled to stop the first job?
    – AMtwo
    Oct 10 at 7:48
  • 1
    If you post the same question independently to multiple forums, at least link them
    – SMor
    Oct 10 at 12:04
  • The SQL Agent job is setup with a schedule to run nightly. When it fails and is re-run we have a disabled schedule to run one time, we simply change that time/date and kick it off again that way. There is not a second job that cancels this job. The job itself calls a stored procedure which itself calls a series of other stored procedures. Oct 11 at 15:28
0

So. I found the smoking gun and it was not expected. A former administrator had put a task scheduler job in that was restarting the SQL Server Agent regularly. Most of the time the ETL process would complete before this task would kick off, but if it just ran a minute or three longer the job would get wacked.

Thank you everyone for the help

New contributor
Carl Christianson is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.