October 9, 2021 at 2:28 pm
So I have an ETL process that runs via SQL Server Agent.
I'd say 20% of the time the process just randomly fails, there is no error in the SQL Server Logs, nor in the SQL Server Agent jobs. The only message is "Msg 0, Sev 0, State 1: Operation canceled [SQLSTATE HY008]"
Re-running the job (which runs the whole process from scratch again) and it completes successfully everytime. How do I get more useful information on what the actual error is. I have seen this error before where it was caused by memory pressure, but I would see that message in the SQL Server logs that there is memory pressure and that it killed the process. So this doesn't seem to be that.
The statement that it fails at is not consistent, but the SQL is all of the variety of select ... into <table name> ... variety. But regardless I need more information on this. Can I do something in the SQL Agent job to increase the tracing level? The message I have above I only have because I'm logging the output of the job to a file. Before I did that all i had was that the job failed. I've also checked windows event logs because this smells like either the O/S or SQL decided that the process needed to be killed. But more info is really needed. thx!
oh and btw, I know that it says cancelled, but this is a job running in the middle of the night via a schedule no one is cancelling it.
October 10, 2021 at 2:49 pm
If SQL Server is restarted - or the server is restarted - then those jobs that were running at the time will show up as canceled. If that is not happening then some other process is killing the running job. Check the SQL Server logs to see what was happening at that time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply