Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

One approach you could take would be to follow the steps in this link - As the article explains, you could set up a job to capture the sql statements and write them to a table, then you would know exactly what was executing when.

share|improve this answer
Link-only answers are considered sub-par. Please provide details here. – EEAA Jun 8 at 16:51

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.