job相关脚本

use msdb
go

--查看某job运行历史信息
select j.name as job_name,
--ja.job_id,
jh.step_id,jh.step_name,jh.server,
       LEFT(cast(jh.run_date as varchar(20)),4)+'-'+SUBSTRING(cast(jh.run_date as varchar(20)),5,2)+'-'+RIGHT(cast(jh.run_date as varchar(20)),2)
       +SPACE(1)
       +LEFT(RIGHT(1000000+cast(jh.run_time as varchar(20)),6),2)+':'
            +SUBSTRING(RIGHT(1000000+cast(jh.run_time as varchar(20)),6),3,2)+':'
            +RIGHT(RIGHT(1000000+cast(jh.run_time as varchar(20)),6),2) as job_started_time,
       +LEFT(RIGHT(1000000+cast(jh.run_duration as varchar(20)),6),2)+':'
            +SUBSTRING(RIGHT(1000000+cast(jh.run_duration as varchar(20)),6),3,2)+':'
            +RIGHT(RIGHT(1000000+cast(jh.run_duration as varchar(20)),6),2) as job_duration ,  --jh.run_duration HHMMSS，比如20000则表示运行了2小时。
[status]=case 
    when jh.run_status=0 then N'failed'--0=失败,1=成功,2=重试,3=已取消
    when jh.run_status=1 then N'Succeeded'
    when jh.run_status=2 then N'retried'
    when jh.run_status=1 then N'canceled'
    else 'Unknown' 
    end,
jh.retries_attempted,jh.message,jh.sql_severity,j.enabled
from msdb.dbo.sysjobs  as j
inner join msdb.dbo.sysjobhistory as jh
on j.job_id=jh.job_id
-- where name='Job_Fedex.UP_FA_CalculatePackageProcessTime' 
order by jh.run_date desc


--作业至少已完成第一步运行,sysjobhistory表中才会有作业历史纪录,若当前作业没有完成任何一个步骤，那表里就不会有本次运行纪录.
--所以作业当前状态用有时无法通过sysjobhistory查看,尤其是作业只有1个步骤且运行时间很长时。
--但是我们可以通过以下两个脚本进行查看这种情况下的某job的当前状态
USE msdb
GO
DECLARE @job_id UNIQUEIDENTIFIER     
SELECT @job_id=job_id FROM dbo.sysjobs  WHERE name='WCMIS085-AdventureWorks2-5'
IF @job_id IS NOT NULL
EXEC xp_sqlagent_enum_jobs  1, 'sa', @job_id   
--state 1 is running,4 finished sucessfully or failed

--返回有关 SQL Server 代理用来在 SQL Server 中执行自动活动的作业的信息。 
exec sp_help_job   @job_name='jobname'
--@execution_status  0 只返回那些空闲的或挂起的作业。1正在执行。 2正在等待线程。  3 在两次重试之间。 4 空闲。 5 挂起。 7 正在执行完成操作。


--查询某正在运行的job当前运行时长及状态
/*
exec sp_configure 'show advanced options',1
RECONFIGURE  WITH OVERRIDE
exec sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH OVERRIDE
*/

if OBJECT_ID('tempdb..#jobinfo') is not null
    drop table #jobinfo

select * into #jobinfo
from openrowset('sqloledb', 'server=(local);trusted_connection=yes','exec msdb.dbo.sp_help_job @job_name=''WCMIS085-AdventureWorks2-5''')

select a.name,
       j.current_execution_status,
       b.start_execution_date,
       DATEDIFF(MI,b.start_execution_date,GETDATE()) as job_duration_minute
  from msdb..sysjobs a
 inner join msdb..sysjobactivity b
    on a.job_id = b.job_id
 inner join #jobinfo j
    on a.job_id = j.job_id    
 where b.start_execution_date is not null
   and b.stop_execution_date is null and a.name='WCMIS085-AdventureWorks2-5'
order by b.start_execution_date desc

--根据job执行命令的关键字筛选出相应的job
select 
 name
,js.job_id
,js.step_id
,command
,enabled
from  
msdb.dbo.sysjobsteps as js with(nolock)
inner join msdb.dbo.sysjobs as j
on js.job_id=j.job_id
where command like '%DBREINDEX%'
or command like '%INDEXDEFRAG%'
or command like '%SP_DBCCINDEX%'
or command like '%INDEX%'




--启用或disable某job
exec sp_update_job @job_name='jobname',@enabled=0

/******************************************************对作业历史记录的一些操作***************************************************************/

--设置作业历史记录数，以下是将记录数社会默认值。所有作业总计纪录条数默认为1000，最多为999999条；单个作业总计记录条数默认为100，最多为999999条。
EXEC msdb.dbo.sp_set_sqlagent_properties 
 @jobhistory_max_rows=-1,
 @jobhistory_max_rows_per_job=-1
GO
--清除所有作业15天前的纪录
DECLARE @OldestDate datetime
SET @OldestDate = GETDATE()-15
EXEC msdb.dbo.sp_purge_jobhistory 
    @oldest_date=@OldestDate
GO
--清除作业”Test”3天前的纪录
DECLARE @OldestDate datetime
DECLARE @JobName varchar(256)
SET @OldestDate = GETDATE()-3
SET @JobName = 'Test'
EXEC msdb.dbo.sp_purge_jobhistory 
    @job_name=@JobName, 
    @oldest_date=@OldestDate

--如果想要保留某些作业历史的记录，可以打开作业属性/步骤/编辑/高级，
--选择将这个步骤的历史记录输出到文件/自定义表中

查看所有作业最后一次运行状态及时长

--查看所有作业最后一次运行状态及时长
if OBJECT_ID('tempdb..#tmp_job') is not null
    drop table #tmp_job

--只取最后一次结果
select job_id,
       run_status,
       CONVERT(varchar(20),run_date) run_date,
       CONVERT(varchar(20),run_time) run_time,
       CONVERT(varchar(20),run_duration) run_duration
  into #tmp_job
  from msdb.dbo.sysjobhistory jh1
 where jh1.step_id = 0
   and (select COUNT(1) from msdb.dbo.sysjobhistory jh2 
        where jh2.step_id = 0 
          and (jh1.job_id = jh2.job_id)
          and (jh1.instance_id <= jh2.instance_id))=1
--select * from #tmp_job

--排除syspolicy_purge_history这个系统作业
select a.name job_name,
       case b.run_status when 0 then 'Failed'
                         when 1 then 'Succeeded'
                         when 2 then 'Retry'
                         when 3 then 'Canceled'
       else 'Unknown' 
       end as job_status,
       LEFT(run_date,4)+'-'+SUBSTRING(run_date,5,2)+'-'+RIGHT(run_date,2)
       +SPACE(1)
       +LEFT(RIGHT(1000000+run_time,6),2)+':'
            +SUBSTRING(RIGHT(1000000+run_time,6),3,2)+':'
            +RIGHT(RIGHT(1000000+run_time,6),2) as job_started_time,
       +LEFT(RIGHT(1000000+run_duration,6),2)+':'
            +SUBSTRING(RIGHT(1000000+run_duration,6),3,2)+':'
            +RIGHT(RIGHT(1000000+run_duration,6),2) as job_duration
  from msdb.dbo.sysjobs a 
  left join    #tmp_job b 
    on a.job_id=b.job_id 
 where a.name not in ('syspolicy_purge_history')
   and a.enabled = 1
 order by b.run_status asc,a.name,b.run_duration desc

检查每个作业的所有步骤最后一次运行状态

--检查每个作业的所有步骤最后一次运行状态

if OBJECT_ID('tempdb..#tmp_job_step') is not null
    drop table #tmp_job_step

select jh1.job_id,
       jh1.step_id,
       jh1.run_status,
       CONVERT(varchar(20),jh1.run_date) run_date,
       CONVERT(varchar(20),jh1.run_time) run_time,
       CONVERT(varchar(20),jh1.run_duration) run_duration
  into #tmp_job_step
  from msdb.dbo.sysjobhistory jh1
 where (select COUNT(1) from msdb.dbo.sysjobhistory jh2 
        where (jh1.job_id = jh2.job_id and jh1.step_id = jh2.step_id)
          and (jh1.instance_id <= jh2.instance_id))=1

select a.name job_name,
       s.step_name,
       case b.run_status when 0 then 'Failed'
                         when 1 then 'Succeeded'
                         when 2 then 'Retry'
                         when 3 then 'Canceled'
       else 'Unknown' 
       end as job_status,
       LEFT(run_date,4)+'-'+SUBSTRING(run_date,5,2)+'-'+RIGHT(run_date,2)
       +SPACE(1)
       +LEFT(RIGHT(1000000+run_time,6),2)+':'
            +SUBSTRING(RIGHT(1000000+run_time,6),3,2)+':'
            +RIGHT(RIGHT(1000000+run_time,6),2) as job_started_time,
       +LEFT(RIGHT(1000000+run_duration,6),2)+':'
            +SUBSTRING(RIGHT(1000000+run_duration,6),3,2)+':'
            +RIGHT(RIGHT(1000000+run_duration,6),2) as job_duration
  from msdb.dbo.sysjobs a 
  left join    #tmp_job_step b 
    on a.job_id=b.job_id
 inner join msdb.dbo.sysjobsteps s
    on b.job_id = s.job_id and b.step_id = s.step_id
 where a.name not in ('syspolicy_purge_history')
   and a.enabled = 1
 order by b.run_status asc,a.name,b.run_duration desc

部分来源以下博客链接

http://www.cnblogs.com/seusoftware/p/3957484.html