Editor's Note: Share your SQL Server tips and tricks with other SQL Server Magazine readers. Email your contributions (400 words or fewer) to firstname.lastname@example.org. If we print your submission, you'll get $50.
Job Schedule Report Revisited
I really like the job schedule report solution from Jose L. Amado-Blanco and Jason Carter (Reader to Reader, "Job Schedule Report," November 2000). However, I ran into some problems with the usp_Scheduled_Jobs stored procedure that generates the report. After compiling and running the stored procedure, I looked at the resulting job schedule report and noticed that several jobs had average run durations that were greater than their max run durations. If you ran a job once, the average and max run durations would be the same, but the average duration should never exceed the max duration.
In delving into the problem, I found that for the max duration, the stored procedure simply formats the number of seconds returned from the max(run_duration) statement. I think the procedure first needs to convert the returned duration to minutes and seconds, then format it. For example, if max(run_duration) returns 351, that value doesn't mean the job's maximum runtime was 3 minutes, 51 seconds. Instead, the value means the maximum runtime was 351 seconds, which is 5.85 minutes or 5 minutes, 51 seconds. The same problem occurs when the stored procedure calculates average run duration.
The stored procedure might also have problems calculating run durations for multistep jobs. For example, let's say that I have a job with three steps and that the sysjobhistory table records two instances of the job's occurrence with the runtimes that Table 1 shows. By usp_Scheduled_Jobs' calculations, the max run duration would be 15, which is the longest run duration of any of the job steps. However, the accurate max run duration of Job1's first occurrence is 25, and the max duration of Job1's second occurrence is 26. So, the max duration of the job is 26, not 15. When you use the stored procedure to calculate average run duration, you get 8.5 (51/6), but the correct average job duration should be 25.5 (51/2).
The authors did some good work. However, I think the stored procedure needs some tweaking.
Thanks for your interest in our usp_Scheduled_Jobs stored procedure, which generates a job schedule report. And thanks for finding a bug that can cause the stored procedure to report jobs with average run durations that are greater than their max run durations. Web Listing 1, which you can download from the SQL Server Magazine Web site (see the More on the Web box for instructions), shows the inaccurate code from the original stored procedure. Web Listing 2 (also available online) shows the fixed code, with Callout A highlighting the modification.
However, the stored procedure does format duration correctly. To understand how our stored procedure presents run duration, let's look at how SQL Server saves run duration information about a job and its steps.
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, we found that SQL Server uses the format hhmmss. So, a run_duration value of 101 means 1 minute, 1 second.
Finally, let's look at your concern about the stored procedure's ability to correctly calculate run durations for multistep jobs. Each time a job runs, sysjobhistory records how much time each step and the whole job takes. For example, running the query
SELECT j.name, jh.step_id, jh.step_name, jh.run_date, jh.run_time, jh.run_duration FROM msdb..sysjobhistory AS jh INNER JOIN msdb..sysjobs AS j ON j.job_id = jh.job_id ORDER BY j.name, jh.run_date, jh.run_time, jh.step_id
on one of our servers produces the report that Figure 1 shows. As you can see in Figure 1, step 0 contains the sum of all the job steps. The stored procedure then returns only the rows where step_id equals 0—that is, the stored procedure returns only records that summarize the job steps.