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在进行每2个job时,都会将SessionID 和Job_ID 写入
msdb.dbo.sysjobactivity 中,由此 msdb.dbo.sysjobactivity 记录当前Agent
正在运营的每1个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;

 

参照文书档案:

A T-SQL query to get current
job activity

SQL Server Agent Tables
(Transact-SQL)

SQL Server Agent Tables
(Transact-SQL)

网站地图xml地图