Jobs, Job History: Run Duration

[rzirpolo]

We have an issue where one of the jobs running is now taking a considerably longer time to complete. All this job does is run a stored procedure via an exec statement.

To query this procedure I have run a trace against it and am now examining the results (output to a table).

Now calculating via the Duration field (measured in milliseconds) in the Trace result and comparing to the Run Duration the two times are very different. I would accept a small difference but we are looking at 20 odd mins here.

So what I'm enquiring about is what is the Run Duration that is listed in the Job History when you go to Management > SQL Server Agent > Jobs.. associated to ?

I would think that it would be how long the procedure in question takes to complete. But if that's the case why is the total Duration in the trace so different ? (I have filtered the trace so that I only measure data regarding the stored procedure).

Take into consideration that this procedure executed in the job calls a number of other procedures within the database when run.

Might sound pointless but it has got me thinking.

 

[rzirpolo]

I have found out some further information in regards to the msdb database. I can query the sysjobhistory table there.

According to SQL Server Books Online (BOL), run_duration is an integer column that is the "amount of time incurred in the execution of the job or step." What BOL doesn't clarify is whether the format is seconds, minutes, or some other unit of measure.

However, by looking at the run_duration column's value and at how the SQL Server Agent reports Run Duration when you view a job's history, I found that SQL Server uses the format hhmmss. So, a run_duration value of 101 means 1 minute, 1 second.