0

As you can see in Log file Viewer it has a column Duration which change for a running job. I want to include this column in my query (from where I get jobname etc)
Any idea to make a SIMPLE script? My only reseacrh so far was sys.dm_exec_sessions and Total_elapsed_time BUT how to connect this to my query where I have my jobname etc?

My query so far;

SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,      
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions
ORDER BY agent_start_date DESC) AND start_execution_date is not null
AND stop_execution_date is null;

enter image description here

1

It seems that, if jobhistory run_duration is null (meaning the job is still running), you could simply calculate the current run_duration using DATEDIFF between jobactivity start_execution_date and sysdatetime().

Since the normal Run_Duration from jobhistory is an int in HHMMSS format, there are some calculations to achieve that.

Something like:

SELECT ja.job_id
    ,j.NAME AS job_name
    ,ja.start_execution_date
    ,ja.stop_execution_date
    ,ISNULL(last_executed_step_id, 0) + 1 AS current_executed_step_id
    ,Js.step_name
    ,CASE 
        WHEN jh.run_duration IS NULL
            THEN 
                convert(int,RIGHT('0' + CAST(datediff(second, ja.start_execution_date, sysdatetime()) / 3600 AS VARCHAR),2)  +
                RIGHT('0' + CAST((datediff(second, ja.start_execution_date, sysdatetime()) / 60) % 60 AS VARCHAR),2)  +
                RIGHT('0' + CAST(datediff(second, ja.start_execution_date, sysdatetime()) % 60 AS VARCHAR),2))
        ELSE jh.run_duration
        END AS run_duration
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id, 0) + 1 = js.step_id
WHERE ja.session_id = (
        SELECT TOP 1 session_id
        FROM msdb.dbo.syssessions
        ORDER BY agent_start_date DESC
        )
    AND start_execution_date IS NOT NULL
    AND stop_execution_date IS NULL;
  • Perfect! But run_duration is in INT and I want to seconds like: ((run_duration / 10000 * 3600 + run_duration % 10000 / 100 * 60 + run_duration % 100)) as RunningSeconds. How to add in the query? – Michael Jun 13 '18 at 11:24
  • @Michael - the calculated run_duration is already in seconds. That's what the DATEDIFF is doing. I guess I don't fully understand what you're asking. – Scott Hodgin Jun 13 '18 at 11:36
  • Of course youre right and Iam wrong in both cases :-) To my defense sysjobhistory har run_duration as INT... – Michael Jun 13 '18 at 11:47
  • @Michael - After looking at your question again and looking at the format of the run_duration from jobhistory, I've updated my answer. – Scott Hodgin Jun 13 '18 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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