SQL Server Agent Jobs History

SQL Server stores Agent job information and execution history in MSDB system tables. SSMS GUI provides a navigation to Jobs and history for all the jobs but sometimes that is not sufficient for deep analysis. The Job history viewer is not easy to get which jobs running longer, steps getting failed, do not have schedules and notification alerts.

Scripts in post will make analysis and auditing of SQL Server Agent Jobs many times easier.

List of currently running jobs and their Status

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Currently running jobs and their Status
 
SELECT  jb.name,
        jb.originating_server ServerName,
        ja.run_requested_date JobStartTime,
        DATEDIFF(MINUTE, ja.run_requested_date, GETDATE()) as TimeElapsed_Minutes,
        case when ja.last_executed_step_id is null
             then 'Step '+Cast (start_step_id as varchar(20)) +' ['+sstep.step_name+'] executing'
             else 'Step ' +Cast ( last_executed_step_id+1 as varchar(20))+' ['+ lstep.step_name
                  + '] executing' 
        end CurrentStepID , sstep.step_name JobStartedfromStep
        , Case ja.run_requested_source when 1 then 'SCHEDULER'
                               when 2 then 'ALERTER'
                               when 3 then 'BOOT'
                               when 4 then 'USER'
                               when 6 then 'ON_IDLE_SCHEDULE' else 'Unknown' end JobRunSource
FROM    msdb.dbo.sysjobs_view jb
        INNER JOIN msdb.dbo.sysjobactivity ja ON jb.job_id = ja.job_id
        INNER JOIN msdb.dbo.syssessions sess ON sess.session_id = ja.session_id
        LEFT JOIN MSDB.DBO.sysjobsteps sstep ON sstep.step_id =start_step_id and sstep.job_id=jb.job_id
        LEFT JOIN MSDB.DBO.sysjobsteps lstep ON lstep.step_id =last_executed_step_id + 1 and lstep.job_id=jb.job_id 
WHERE   stop_execution_date IS NULL 
        AND run_requested_date IS NOT NULL

Execution time history for one month per Job in minutes

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Jobs execution time in minutes 

SELECT jb.name as 'JobName'
 , msdb.dbo.agent_datetime(jh.run_date, jh.run_time) as JobRunDate
 , ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60 + jh.run_duration%100 + 31 ) / 60) AS JobRunDuration_Minutes
 , Case jh.run_status when 0 then 'Failed'
                              when 1 then 'Succeeded'
                              when 2 then 'Retry'
                              when 3 then 'Stopped' else 'Unknown' end  JobRunStatus                           
FROM msdb.dbo.sysjobs jb 
INNER JOIN msdb.dbo.sysjobhistory jh  ON jb.job_id = jh.job_id 
WHERE step_id=0 -- '(Job outcome)'
AND DATEDIFF(MM, msdb.dbo.agent_datetime(jh.run_date, jh.run_time), GETDATE())=0 --- One Month
AND ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60
         + jh.run_duration%100 + 31 ) / 60)> 5 -- Greater than 5 mins 
AND jb.name='Job infinite loop'                                                            
ORDER BY JobName, JobRunDate DESC

Execution time history for one month per Job Step in minutes

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Job Steps execution time in minutes 

SELECT rank() over(ORDER BY jb.name,msdb.dbo.agent_datetime(jh.run_date, jh.run_time) desc) JobExecutionID
,jb.name as 'JobName'
, jstp.step_name as 'StepName'
, msdb.dbo.agent_datetime(jh.run_date, jh.run_time) as 'JobRunDate'
, ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60 + jh.run_duration%100 + 31 ) / 60) AS 'JobRunDurationMinutes'
, Case jh.run_status when 0 then 'Failed'
                              when 1 then 'Succeeded'
                              when 2 then 'Retry'
                              when 3 then 'Stopped' else 'Unknown' end  JobRunStatus                             
FROM msdb.dbo.sysjobs jb 
INNER JOIN msdb.dbo.sysjobsteps jstp ON jb.job_id = jstp.job_id
INNER JOIN msdb.dbo.sysjobhistory jh  ON jb.job_id = jh.job_id 
WHERE jh.step_id >0 -- '(Job outcome)'
AND DATEDIFF(MM, msdb.dbo.agent_datetime(jh.run_date, jh.run_time), GETDATE())=0 --- One Month
AND ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60                                    
        + jh.run_duration%100 + 31 ) / 60)> 5 -- Greater than 5 mins
AND jb.name='Job infinite loop'
ORDER BY JobName, JobRunDate DESC

Get job history last executed by whom

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Get job history last executed by whom  

SELECT case jb.[enabled] when 1 then 'Active' else 'Inactive' end JobCurrentStatus
, jb.name JobName , ja.start_execution_date LastExecutionDate
, Case ja.run_requested_source when 1 then 'SCHEDULER'
                               when 2 then 'ALERTER'
                               when 3 then 'BOOT'
                               when 4 then 'USER'
                               when 6 then 'ON_IDLE_SCHEDULE' else 'Unknown' end 'JobRunSource'
,Case jh.run_status when 0 then 'Failed'
                              when 1 then 'Succeeded'
                              when 2 then 'Retry'
                              when 3 then 'Stopped' else 'Unknown' end  JobRunStatus

FROM msdb.dbo.sysjobs AS jb
INNER JOIN msdb.dbo.sysjobactivity AS ja ON ja.job_id = jb.job_id
INNER JOIN msdb.dbo.sysjobhistory jh on jh.instance_id=ja.job_history_id
WHERE exists (  SELECT sja.job_id,MAX(sja.start_execution_date)
                FROM msdb.dbo.sysjobactivity sja
                WHERE start_execution_date IS NOT NULL
                AND sja.job_id=ja.job_id
                AND sja.start_execution_date=ja.start_execution_date
                group by sja.job_id
              )
ORDER BY ja.start_execution_date DESC

Get jobs list by notification alter operators

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Get jobs list by notification alter operators 

SELECT jb.name JobName, 
CASE notify_level_email WHEN 0 THEN 'Never'
WHEN 1 THEN 'When the job succeeds'
WHEN 2 THEN 'When the job fails'
WHEN 3 THEN 'Whenever the job completes (regardless of the job outcome)' 
ELSE 'Unknown' END  'NotificationEvent'
, isnull(op.name ,'') OperatorName
, Case WHEN op.name is not null THEN Case op.[enabled] when 1 then 'Active' else 'Inactive' end  ELSE '' END 
FROM msdb.dbo.sysjobs jb left join msdb.dbo.sysoperators op
on jb.notify_email_operator_id=op.id
ORDER BY isnull(op.name ,''), jb.name