SCRIPT: How long SQL Agent job is running?

on
 SELECT                
    CONVERT(VARCHAR(12), DATEDIFF(SECOND,aj.start_execution_date,GetDate()) /60/60/24) + ' Day(s), '
    + CONVERT(VARCHAR(12), DATEDIFF(SECOND,aj.start_execution_date,GetDate()) /60/60 % 24)
    + ':' + RIGHT('0' + CONVERT(VARCHAR(2),  DATEDIFF(SECOND,aj.start_execution_date,GetDate()) /60 % 60), 2)
    + ':' + RIGHT('0' + CONVERT(VARCHAR(2),  DATEDIFF(SECOND,aj.start_execution_date,GetDate()) % 60), 2)
FROM
       msdb..sysjobactivity aj
       JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE
       aj.stop_execution_date IS NULL -- job hasn't stopped running
       AND aj.start_execution_date IS NOT NULL -- job is currently running
       AND sj.name = 'SQL_JOB_NAME'
       AND NOT EXISTS( -- make sure this is the most recent run
    SELECT 1
    FROM msdb..sysjobactivity new
    WHERE new.job_id = aj.job_id
    AND new.start_execution_date > aj.start_execution_date
)

Comments

Post a Comment