SQLTeam.com | Weblogs | Forums

Need help with last_run_duration from msdb..sysjobsteps


#1

Hi guys!

I've developed a stored procedure to send custom emails and I want to display total duration time of the job into email but...as long my stored procedure which sends emails is last step of the job, I think I can't get a total duration time of the job while it wasn't completed.

So, I tried to SUM all steps duration time but when I SUM 00:01:37 and 00:01:36 values I will have 00:02:70!
Converting last_run_duration value (int) to varchar I used this formula:
STUFF(STUFF(RIGHT('000000' + CAST(last_run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')

How to convert 00:02:70 to 00:03:10 in this case or any other case such 70 minutes to 1 hour and 10 minutes and so on?

Or do you have any idea how to retrieve SQL job duration time without been finished?

Thanks.


#2

CAST it to a date time, then you can add it up, something like this:

SUM(CAST(STUFF(STUFF(RIGHT(‘000000’ + CAST(last_run_duration AS VARCHAR(6)), 6), 3, 0, ‘:’), 6, 0, ‘:’) AS datetime))


#3

Unfortunately, I receive this:

Operand data type datetime is invalid for sum operator.


#4

Oops, duh, yeah, I responded too quickly before. With more thought, something like this:

select job_start.job_id, job_start.last_run_date as last_run_start_date, job_start.last_run_time as last_run_start_time,
    SUM(
        js.last_run_duration / 10000 * 60 + (js.last_run_duration % 10000 / 100) + CASE WHEN js.last_run_duration % 100 >= 30 THEN 1 ELSE 0 END
        ) AS last_run_mins
from (
    select top (1) job_id, last_run_date, last_run_time
    from msdb.dbo.sysjobsteps
    where job_id = (select job_id from msdb.dbo.sysjobs where name = '<your_job_name>') and
        step_id = 1
    order by last_run_date desc, last_run_time desc
) as job_start
inner join msdb.dbo.sysjobsteps js on js.job_id = job_start.job_id and (js.last_run_date > job_start.last_run_date or 
    js.last_run_date = job_start.last_run_date and js.last_run_time >= job_start.last_run_time)
group by job_start.job_id, job_start.last_run_date, job_start.last_run_time

#5

Scott, your script it's great but one question: why did you round up last_run_duration? Can you please tell me what to modify in order to receive 00:01:37 minutes and so on?

Running your query will round up 00:01:37 minutes to 2 minutes.

Thank you!

L.E.: I figured out how to display minutes and seconds (no round up) but still have the case with 00:02:70.


#6

I thought you just wanted minutes, not down to secs. Here's one with seconds:

select job_id, last_run_start_date, last_run_start_time,
    case when last_run_secs >= 86400 then cast(last_run_secs / 86400 as varchar(5)) + 'd+' else '' end +
    convert(varchar(8), dateadd(second, last_run_secs % 86400, 0), 108) as last_run_duration
from (
    select job_start.job_id, job_start.last_run_date as last_run_start_date, job_start.last_run_time as last_run_start_time,
        SUM(js.last_run_duration / 10000 * 3600 + (js.last_run_duration % 10000 / 100) * 60 + js.last_run_duration % 100) as last_run_secs
    from (
        select top (1000) job_id, last_run_date, last_run_time
        from msdb.dbo.sysjobsteps
        where --job_id = (select job_id from msdb.dbo.sysjobs where name = '<your_job_name>') and
            step_id = 1
        order by last_run_date desc, last_run_time desc
    ) as job_start
    inner join msdb.dbo.sysjobsteps js on js.job_id = job_start.job_id and (js.last_run_date > job_start.last_run_date or 
        js.last_run_date = job_start.last_run_date and js.last_run_time >= job_start.last_run_time)
    group by job_start.job_id, job_start.last_run_date, job_start.last_run_time
) as derived

#7

Scott, you're the man. It's perfect!

Thank you so much!