asked
SQL Agent Job Fails in the middle of the job
I have an agent job that normally runs weekly on a scheduled time and it usually runs without any problem. I ran the same job twice manually by right clicking the job and clicked “start job” but both times the job failed in the middle of the job but at a different stage of the job. I’m a member of a sysadmin and the job runs under a service account. I’m running Microsoft SQL Server 2012 (SP1) on a Windows Server 2012. I looked at the audit log and received this error “Msg 0, Sev 0, State 1: Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000]” I’m not sure if there’s a way I can look whether I lost a sql server connection but I looked at the windows server uptime and it was up and running during that time. Is there a better way to track this problem and see what is causing the job to fail at a different stage each time when I run it manually?
Short term, also check the error log on SQL Server to see if you can see any errors. Longer term, modify the step (or steps) of the job to include an output file. You just have to go to the advanced tab on the step and define an output file. That will show you a lot more than just the final error message. It should make it possible to know exactly what went wrong.
I have the output file and that is where I got the message: "Msg 0, Sev 0, State 1: Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000]". I meant to say output file rather than audit log in my original question. I looked at the sys.dm_exec_sessions and there's a program name "SQLAgent - Job invocation engine" started a session around the same time when the job failed/stopped.
Ah, well, that was the one that usually gives me the information I need. Dropped connections are somewhat tough to troubleshoot. You could try turning on extended events to watch the process as it occurs. That's probably what I would do.
15 People are following this question.
