SQLSERVER 作业

作业系统表

    与作业有关的系统表、视图大致有下面9个，下面就不费口舌详细解说每一个系统表的作用了，MSDN文档上有详细的解说，有兴趣的翻看一下即可。

 

SELECT * FROM msdb.dbo.sysjobs            --存储将由 SQL Server 代理执行的各个预定作业的信息

SELECT * FROM msdb.dbo.sysjobschedules    --包含将由 SQL Server 代理执行的作业的计划信息

SELECT * FROM msdb.dbo.sysjobactivity;    --记录当前 SQL Server 代理作业活动和状态

SELECT * FROM msdb.dbo.sysjobservers      --存储特定作业与一个或多个目标服务器的关联或关系

SELECT * FROM msdb.dbo.sysjobsteps;       --包含 SQL Server 代理要执行的作业中的各个步骤的信息

SELECT * FROM msdb.dbo.sysjobstepslogs;   --包含所有 SQL Server 代理作业步骤的作业步骤日志

SELECT * FROM msdb.dbo.sysjobs_view;      --

SELECT * FROM msdb.dbo.sysjobhistory      --包含有关 SQL Server 代理执行预定作业的信息

SELECT * FROM msdb.dbo.syscategories      --包含由 SQL Server Management Studio 用来组织作业、警报和操作员的类别

-- 根据SP来检查用的那个作业
SELECT *
FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )
ON STP .job_id = JOB .job_id
WHERE STP .command LIKE N'% sp_name %'

 

-- 禁用JOB
EXEC msdb..sp_update_job
    @job_name = 'Job_name',
    @enabled =0        -- 0为禁用，1为启用

-- 删除JOB ,具体参数查 MSDN,此处不再多说
EXEC msdb.dbo.sp_delete_job  @job_name = 'Job_name';  -- job_name 参看select name  from msdb.dbo.sysjobs
EXEC msdb.dbo.sp_start_job @job_name='Job_name'
EXEC msdb.dbo.sp_stop_job    @job_name='Job_name'    

-- 查询 已经启用 Job 的执行状态
select  b.name,   a.step_name, msdb.dbo.agent_datetime( run_date, run_time) AS 'RunDateTime' ,  
        a.run_duration,  
         case  when a.run_status=0 then 'Failed'  
         when a.run_status= 1 then 'Succeeded'
         when a.run_status= 2 then 'Retry'
         when a.run_status= 3 then 'Canceled'
         else 'Unknown'  
         end as run_status,  
       a.[message]  
from msdb .dbo. sysjobhistory a inner join msdb .dbo. sysjobs b    on a.job_id =b .job_id 
inner join msdb. dbo.sysjobsteps s on a .job_id = s .job_id and a.step_id = s .step_id
where b .enabled = 1 

--查看历史情况
select run_status,a.run_time, a.* 
    from msdb.dbo.sysjobhistory a 
        inner join msdb.dbo.sysjobs b on a.job_id=b.job_id
    where b.name='作业名'

作业的执行状态run_status： 
0 = 失败
1 = 成功
2 = 重试
3 = 取消
4 = 正在进行

--------------

create view V_JOB_CHECK
as
with T1 as
(
select category = jc.name, category_id = jc.category_id, job_name = j.name, 
job_enabled = j.enabled, 
last_run_time = cast(js.last_run_date as varchar(10)) + '-' + cast(js.last_run_time as varchar(10)), 
last_run_duration = js.last_run_duration, 
last_run_status = js.last_run_outcome, 
last_run_msg = js.last_outcome_message + cast(nullif(js.last_run_outcome,1) as varchar(2)), 
job_created = j.date_created, 
job_modified = j.date_modified 
from msdb.dbo.sysjobs j 
inner join msdb.dbo.sysjobservers js on j.job_id = js.job_id 
inner join msdb.dbo.syscategories jc on j.category_id = jc.category_id 
where j.enabled = 1 and js.last_run_outcome in (0,1,3,5) 
),
-- 0:Fail 1:Succ 3:Cancel 5:First run and jc.category_id not between 10 and 20 -- repl 
T2 as
(
select job_name as 作业名称,case job_enabled when 1 then '已启动' when 0 then '已禁用' end as 状态,
last_run_time as 执行时间,
case last_run_status when 0 then '失败' 
                     when 1 then '成功'
                     when 2 then '重试'
                     when 3 then '取消'
                     when 4 then '正在进行'
end  as 执行状态
from T1 
)

select 作业名称,状态,tdate+' '+
case LEN(tTime) when 5 then '0'+LEFT(tTime,1)+':'+substring(tTime,2,2)+':'+substring(tTime,4,2)
               when 6 then LEFT(tTime,2)+':'+substring(tTime,3,2)+':'+substring(tTime,5,2)
               end as 执行时间,
执行状态 from (
select *,convert(nvarchar(10),convert(datetime,left(执行时间,PATINDEX('%-%', 执行时间)-1)),120) as tdate
,substring(执行时间,PATINDEX('%-%', 执行时间)+1,LEN(执行时间)) as Ttime
 from T2
 )a
 
 
 --作业的执行状态run_status： 
--0 = 失败
--1 = 成功
--2 = 重试
--3 = 取消
--4 = 正在进行