I am trying to figure out how to troubleshoot a situation with SQL Server Agent (2012). A job that has been running nightly (user SQL Server Agent as its scheduler) for over a year in 2 to 4 seconds recently ran for over 7 hours?
I cannot find anything in the Servers SQL logs or the servers event logs to explain the long run. The timing also does not appear to be workload related as the work done by the job is essentially the same every night (daily totals used for billing). The job also completed correctly. (although it caused other issues but they were related to the job not being designed to run over multiple dates which is what it ended up doing)
Step 2 of the job calculates and then writes a series of records with time stamps. All the expected records were written in about 2 seconds. That is all the step does so it appears to have completed quickly, but the SQL agent log indicates that that particular step took 7 hours and 30 minutes to run. Also the next step completed at that time.
Is there any way to get more information on what SQL was doing for that time? Alternatively is it possible to limit the total time that a job will run? in other words make it error out so at least we will know.
I have run a dbcc check DB (no errors).
SQL version is 11.03339 OS is Win 2008R2 Enterprise
The issue has only occurred once so far.
Thanks in advance for any assistance.