查看Job执行的历史记录

刮骨剑 2018-12-11

展开全文

SQL Server将Job的信息存放在msdb中，Schema是dbo，表名以“sysjob”开头。

一，基础表

1, 查看Job和Step，Step_ID 是从1 开始的。

select j.job_id,j.name,j.enabled,j.description,
    j.start_step_id,j.date_created,j.date_modified
from msdb.dbo.sysjobs j with(nolock)
where name =N'xxx'

2, 查看特定job 的所有 Step的执行记录，Step_id=0 记录job的整体执行情况；run_time 和 run_duration 是int类型，格式是hhmmss。

select jh.instance_id,jh.job_id,jh.step_id,jh.step_name,jh.sql_message_id,jh.sql_severity,
    jh.message,
    case jh.run_status
        when 0 then 'failed'
        when 1 then 'Succeeded'
        when 2 then 'Retry'
        when 3 then 'Canceled'
    end as run_status,
    jh.run_date,jh.run_time,jh.run_duration
from msdb.dbo.sysjobhistory jh with(nolock)
where job_id=N'07A53839-E012-4C80-9227-15594165B013'
order by instance_id desc

3，Job History的查询

use msdb
go

--查看job 最后一次执行的情况
DECLARE @Job_ID uniqueidentifier;

select @Job_ID=j.job_id
from msdb.dbo.sysjobs j with(nolock)
where j.name=N'job name'

;with cte as
(
select jh.job_id,
    jh.run_date,
    jh.run_time,
    jh.run_status,
    ROW_NUMBER() over(PARTITION by jh.job_id order by jh.run_date desc,jh.run_time desc) as rid
from msdb.dbo.sysjobhistory jh with(NOLOCK)
where jh.step_id=0
    and jh.job_id=@Job_ID
)
select j.name as JobName,
    jh.step_id,
    jh.step_name,
    case jh.run_status 
        when 0 then 'Failed' 
        when 1 then 'Successed' 
        when 2 then 'Retry' 
        when 3 then 'Canceled' 
        else N'' 
    end as StepStatus,
    jh.message,
    cast(STUFF(STUFF(str(jh.run_date,8),7,0,N'-'),5,0,N'-') + N' ' + 
         STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),N' ',N'0'),5,0,N':'),3,0,N':') 
         AS DATETIME) as [StartTime],
    stuff(stuff(replace(str(jh.run_duration,6),N' ',N'0'),5,0,N':'),3,0,N':')  as Duration
from  msdb.dbo.sysjobs j with(nolock)
inner join msdb.dbo.sysjobhistory jh with(nolock)
    on jh.job_id=j.job_id
inner join cte as c
    on c.job_id=jh.job_id and jh.run_date>=c.run_date and jh.run_time>=c.run_time and jh.step_id>0
where c.rid=1
order by jh.step_id asc

4，通过msdb.dbo.sysjobsteps 查看指定Job中每个step 最后执行的状态

select js.job_id,js.step_id,js.step_name,
    js.subsystem,js.command,
    js.last_run_outcome,--Last Run Result
    js.last_run_duration,
    js.last_run_date,
    js.last_run_time,
    js.last_run_retries
from msdb.dbo.sysjobsteps js with(nolock)
where js.job_id=N'DF0C68ED-7C76-4571-A72D-CD6161EFFC04'

5，查看每个Job最后一次执行的状态和该job最后一个Step的执行信息。

use msdb
GO

;with cte_job as
(
select jh.server,
    j.name,
    j.enabled ,
    jh.job_id,
    jh.run_status,
    jh.run_date,
    jh.run_time,
    jh.run_duration,
    ROW_NUMBER() OVER(PARTITION by jh.job_id order by jh.run_date desc,jh.run_time desc ) as rid
from msdb.dbo.sysjobs  j with(nolock)
inner join msdb.dbo.sysjobhistory jh with(nolock)
    on j.job_id=jh.job_id
where j.category_id=0
    and jh.step_id=0
)
SELECT j.name,
    j.enabled,
    case j.run_status 
        when 0 then 'Failed' 
        when 1 then 'Successed' 
        when 2 then 'Retry' 
        when 3 then 'Canceled' 
        else '' 
    end as [Status],
    cast(STUFF(STUFF(str(j.run_date,8),7,0,N'-'),5,0,N'-') + N' ' + 
         STUFF(STUFF(REPLACE(STR(j.run_time,6,0),N' ',N'0'),5,0,N':'),3,0,N':') 
         AS DATETIME) as [StartTime],
    stuff(stuff(replace(str(j.run_duration,6),N' ',N'0'),5,0,N':'),3,0,N':')  as Duration,
    jh.step_id,
    jh.step_name ,
    case jh.run_status 
        when 0 then 'Failed' 
        when 1 then 'Successed' 
        when 2 then 'Retry' 
        when 3 then 'Canceled' 
        else N'' 
    end as StepStatus,
    jh.message as StepMessage
from cte_job j
outer apply
(
select top 1 jh.step_id,jh.step_name,jh.run_status,jh.run_date,jh.run_time,jh.run_duration,jh.message
from msdb.dbo.sysjobhistory jh with(nolock)
where j.job_id=jh.job_id and jh.step_id>0 and jh.run_date>=j.run_date and jh.run_time>=j.run_time
order by jh.step_id desc
) as jh
where j.Rid=1 --Last Execution
    and j.run_status in(0,2)  --0 = Failed, 2=retry
order by j.name

二，查看Running jobs

Agent在运行时，会创建一个Session，并将current SessionID存储在msdb.dbo.syssessions 中。Agent在执行每一个job时，都会将SessionID 和Job_ID 写入 msdb.dbo.sysjobactivity 中，因此 msdb.dbo.sysjobactivity 记录当前Agent 正在运行的每一个Job的信息（Job开始执行的时间，执行成功的最后一个StepID....)，如果要查看Agent当前执行的Job，那么msdb.dbo.sysjobactivity的SessionID必须是当前Agent使用的SessionID。

1,基础表

msdb.dbo.syssessions

Each time SQL Server Agent starts, it creates a new session. SQL Server Agent uses sessions to preserve the status of jobs when the SQL Server Agent service is restarted or stopped unexpectedly. Each row of the syssessions table contains information about one session. Use the sysjobactivity table to view the job state at the end of each session. Every time the agent is started a new session_id is added to the syssessions table.

msdb.dbo.sysjobactivity

Records current SQL Server Agent job activity and status. The column last_executed_step_id is the id of the last step completed. If the job is on the first step it’s NULL. So getting the current step is a simple formula of ISNULL(last_executed_step_id,0)+1.

2，查看当前正在运行的Job

SELECT
    j.name AS job_name,
    ja.start_execution_date, 
    ISNULL(ja.last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM msdb.dbo.sysjobactivity ja with(nolock)
LEFT JOIN msdb.dbo.sysjobhistory jh with(nolock)
    ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j 
    ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;