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.