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.
--- 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
