use msdbgo--查看某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.enabledfrom msdb.dbo.sysjobs  as jinner join msdb.dbo.sysjobhistory as jhon j.job_id=jh.job_id-- where name=‘Job_Fedex.UP_FA_CalculatePackageProcessTime‘ order by jh.run_date desc--作业至少已完成第一步运行,sysjobhistory表中才会有作业历史纪录,若当前作业没有完成任何一个步骤，那表里就不会有本次运行纪录.--所以作业当前状态用有时无法通过sysjobhistory查看,尤其是作业只有1个步骤且运行时间很长时。--但是我们可以通过以下两个脚本进行查看这种情况下的某job的当前状态USE msdbGODECLARE @job_id UNIQUEIDENTIFIER     SELECT @job_id=job_id FROM dbo.sysjobs  WHERE name=‘WCMIS085-AdventureWorks2-5‘IF @job_id IS NOT NULLEXEC 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‘,1RECONFIGURE  WITH OVERRIDEexec sp_configure ‘Ad Hoc Distributed Queries‘,1RECONFIGURE WITH OVERRIDE*/if OBJECT_ID(‘tempdb..#jobinfo‘) is not null    drop table #jobinfoselect * into #jobinfofrom 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执行命令的关键字筛选出相应的jobselect  name,js.job_id,js.step_id,command,enabledfrom  msdb.dbo.sysjobsteps as js with(nolock)inner join msdb.dbo.sysjobs as jon js.job_id=j.job_idwhere command like ‘%DBREINDEX%‘or command like ‘%INDEXDEFRAG%‘or command like ‘%SP_DBCCINDEX%‘or command like ‘%INDEX%‘--启用或disable某jobexec 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=-1GO--清除所有作业15天前的纪录DECLARE @OldestDate datetimeSET @OldestDate = GETDATE()-15EXEC msdb.dbo.sp_purge_jobhistory     @oldest_date=@OldestDateGO--清除作业”Test”3天前的纪录DECLARE @OldestDate datetimeDECLARE @JobName varchar(256)SET @OldestDate = GETDATE()-3SET @JobName = ‘Test‘EXEC msdb.dbo.sp_purge_jobhistory     @job_name=@JobName,     @oldest_date=@OldestDate--如果想要保留某些作业历史的记录，可以打开作业属性/步骤/编辑/高级，--选择将这个步骤的历史记录输出到文件/自定义表中