none
SSMS Job Activity Monitor - Log File viewer shows Duration column for running jobs - How to include it in my script?

    Question

  • As you can see in Picture  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;


    MIchkarlsson



    Wednesday, June 13, 2018 9:39 AM

All replies

  • Hello Michael,

    Please run below query and let me know if it meets your requirement.

    SELECT
        j.name,
        h.run_status,
        durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
            ' ','0'),4,0,':'),7,0,':'),
        [start_date] = CONVERT(DATETIME, RTRIM(run_date) + ' '
            + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),
            ' ','0'),3,0,':'),6,0,':'))
    FROM
        msdb.dbo.sysjobs AS j
    INNER JOIN
        (
            SELECT job_id, instance_id = MAX(instance_id)
                FROM msdb.dbo.sysjobhistory
                GROUP BY job_id
        ) AS l
        ON j.job_id = l.job_id
    INNER JOIN
        msdb.dbo.sysjobhistory AS h
        ON h.job_id = l.job_id
        AND h.instance_id = l.instance_id
    ORDER BY
        CONVERT(INT, h.run_duration) DESC,
        [start_date] DESC;

    Please mark me as answer if my post resolves your issue.

    Br

    ChetanV

      
    Wednesday, June 13, 2018 9:47 AM
  • Hi and thanks. But it doensnt solve my problem. You har taking historical data from sysjobhistory and that table with column run_duration gets written when the job is done (fail or success)

    Have a look at log file viewer and a job that is running and column Running, make a refresh and you will see the Running column data has changed after your refresh. 

    Maybe get a start DateTime and just add  datediff from from sysdatetime into a new column ? But I dont know how to write that in my query. any help would be grateful.

    Its real time data I want to put in my dashboard that itself refresh often

     

    MIchkarlsson


    Wednesday, June 13, 2018 11:15 AM