语句获取作业属性、历史记录

1、作业所有者

--作业所有者
select sj.name,sj.date_created,sj.date_modified,ssp.name OwnName from msdb.dbo.sysjobs sj
left join sys.server_principals ssp
on sj.owner_sid=ssp.sid
--where ssp.name='loginname'
order by sj.name

2、禁用、调度未启用的作业

--禁用、调度未启用的作业
select case when a.enabled=0 and c.enabled=0 then 'JobAndScheduleDisabled'
  when a.enabled=0 and c.enabled<>0 then 'JobDisabled'
  when a.enabled<>0 and c.enabled=0 then 'ScheduleDisabled' end as DisabledType
,a.name,a.date_created,a.date_modified from msdb.dbo.sysjobs a
inner join msdb.dbo.sysjobschedules b
on a.job_id=b.job_id
inner join msdb.dbo.sysschedules c
on b.schedule_id=c.schedule_id
where a.enabled=0 or c.enabled=0
order by a.name,a.enabled

3、历史记录(代理-属性-历史记录，可设置总日志最大行数和单个作业最大行数)查看，作业最早执行时间、最晚执行时间、执行次数，结合作业步骤数可大致知晓是否能继续保存新的日志记录且不删除旧的日志记录。

--作业执行历史记录
select a.*,a.JobRunTimes*(b.StepCount+1) HistoryRecords from(
SELECT sj.name,COUNT(sj.name) JobRunTimes
,min(msdb.dbo.agent_datetime(run_date,run_time)) FirstRunDateTime--05及以上版本可直接调用msdb.dbo.agent_datetime
,max(msdb.dbo.agent_datetime(run_date,run_time)) LastRunDateTime
FROM  msdb.dbo.sysjobhistory sjh
INNER JOIN msdb.dbo.sysjobs sj
ON sjh.job_id=sj.job_id
WHERE sjh.step_id=0 AND sj.enabled=1
GROUP BY sj.name,sj.job_id) a
inner join (
select sj.name,COUNT(sjs.step_id) StepCount from msdb.dbo.sysjobs sj
inner join msdb.dbo.sysjobsteps sjs
on sj.job_id=sjs.job_id
group by sj.name) b
on a.name=b.name
order by a.FirstRunDateTime

4、作业最后一次执行时间、执行时长

--作业最后一次执行时间、执行时长
select b.name
,case when last_run_date=0 then ''
else msdb.dbo.agent_datetime(last_run_date,last_run_time) end last_run_datetime
,convert(varchar,msdb.dbo.agent_datetime('20151201',last_run_duration),108) last_run_duration
from [msdb].[dbo].[sysjobservers] a
inner join [msdb].[dbo].[sysjobs] b
  on a.job_id=b.job_id
where b.enabled=1  
  order by last_run_duration desc

5、查看指定作业历史记录

--查看指定作业历史记录
select top 20 sj.name,sjh.step_id,sjh.message,sjh.run_status
      ,msdb.dbo.agent_datetime(run_date,run_time) run_time
      ,convert(varchar,msdb.dbo.agent_datetime(run_date,run_duration),108) run_duration
  from msdb.dbo.sysjobhistory sjh
 inner join msdb.dbo.sysjobs sj
    on sjh.job_id=sj.job_id
 where sj.name='JobName'
 order by msdb.dbo.agent_datetime(run_date,run_time) desc