SQLAgent job randomly cancels

Question

We have an ETL process that runs nightly via SQL Agent. I'd say about 1 out of 5 times the job just stops. The status is failed in the job history but no real error message to see. Using an output file the only message I get is: "Msg 0, Sev 0, State 1: Operation canceled [SQLSTATE HY008]"

If i re-run the job it will start over and run successfully. Looking at that message the only thing I see is that basically an async job was told to cancel and did (para pharasing). There are no errors in the SQL Server log, windows logs, sql agent logs. Nothing that helps me figure out why this job was cancelled. It also doesn't always cancel at the same spot.

The ETL process we use is pretty much a series of sprocs that get called that use select ... into ... < table > type of statements. Nothing too fancy going on. It's completely maddening as it's not consistent in any way. How do I get more information on WHY the job was cancelled? Really any more info would be better then I have now. Very frustrated with sql server/windows at the moment...

Answer 1

Hi Amelia, thx.

So I figured out what was happening. Apparently a former sysadmin had put in (do not know why you would do this) a task scheduler job in that kills and restarts the SQL Server Agent. The ETL job more times then not would complete before that job would kick off, but if it ran just a few minutes long it would get wacked. So basically the SQL Cancel message was actually spot on, if not helpful on figuring out what was issuing the cancel request. It would be great if somehow the SQL Cancel request could have more information to help track that origin down. Previously I had seen that error on a memory pressure issue where a job would get cancelled due to that. In SQL Server 2017 how do (in transact sql) enable extended events to capture more info. I admit to looking and not quite finding it.
thx
Carl