Agent Job 是SQL Server提供的自动管理工具，用户可以创建一个Job，设置Schedule，这样SQL Server Agent就会在指定的时间自动执行任务。一个任务可以是一段TSQL脚本，也可以是一个SSIS Package。SQL Server把Job每一次执行的历史信息存放在系统数据库msdb中。
Agent Job由Job Step，Schedule 和 Notification构成，本文简单介绍Job和Step的基本信息。
一，Agent Job的基本信息
Agent Job实际上是Job Step的容器，每一个Step都是一个待执行的任务，为了便于区分，Job的每一个Step都有一个编号，从1开始，依次递增。特殊地，step_id=0 代表Job的整体执行情况，Step名称为 (Job outcome)。
1，Job的基本信息
通过 msdb.dbo.sysjobs来查看Job的基本信息，第一个Step的ID是1，第一个开始运行的Step叫做Start Step，通常情况下，Start Step是从1 开始的。
select j.job_id
,j.name as job_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的基本信息
通过msdb.dbo.sysjobsteps 来查看Step的基本信息，常用的字段：
- subsystem：子系统的名称，常见的是TSQL 和 SSIS
- command：子系统执行的命令
- last_run_outcome：step上一次执行的结果，0 = Failed，1 = Succeeded，2 = Retry，3 = Canceled，5 = Unknown
- last_run_duration ：step上一次执行使用的时间，结果的格式是hhmmss
- last_run_retries：step上一次重试的次数
- last_run_date 和 last_run_time：step上一次开始执行的日期和时间，结果的格式分别是：yyyyMMdd 和 hhmmss
通过msdb.dbo.sysjobsteps 可以查看到上一次执行的状态：
select js.job_id
,js.step_id
,js.step_name
,js.subsystem
,js.command
,js.last_run_outcome
,case js.last_run_outcome
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
else 'Unknown'
end as last_run_outcome_descr
,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'375ED3A3-97D5-4B10-924F-4E82C8F49B45'
and js.step_id=1
3，Job执行的历史消息
通过msdb.dbo.sysjobhistory来查看job step的历史消息，常用的字段：
- instance_id：每一条记录都是一个实例
- job_id和 step_id：job的id和step的id，注意 step_id=0表示整个Job
- run_status：job step运行的状态，0 = Failed, 1 = Succeeded, 2 = Retry, 3 = Canceled, 4 = In Progress（很少存在run_status=4的情况）
- run_date 和 run_time：job step运行的日期和时间，格式分别是：yyyyMMdd 和 hhmmss
- run_duration：job step运行的总时间，格式是hhmmss
注意：在大多数情况下，正在执行的job step不会创建history instance，即msdb.dbo.sysjobhistory中很少存在run_status=4的情况。绝大多数情况下，只有job step运行完成之后，才会创建一个history instance。
查看Job Step运行的所有历史记录，每一个step都是一个实例（Instance），表示一条历史记录。
select jh.instance_id
,jh.job_id
,jh.step_id
,jh.step_name
,jh.message
,case jh.run_status
when 0 then 'failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In Progress'
end as run_status
,jh.run_date
,jh.run_time
,jh.run_duration
from msdb.dbo.sysjobhistory jh with(nolock)
where job_id=N'375ED3A3-97D5-4B10-924F-4E82C8F49B45'
order by instance_id desc
二，Job History的查询
查看Job执行的历史记录
select jh.instance_id
,jh.job_id
,cast(stuff(stuff(str(jh.run_date,8),7,0,'-'),5,0,'-') + ' ' +
stuff(stuff(replace(str(jh.run_time,6,0),' ',''),5,0,':'),3,0,':')
as datetime) as job_start_datetime
,stuff(stuff(replace(str(jh.run_duration,6),' ',''),5,0,':'),3,0,':') as job_run_duration
,jh.step_id
,jh.step_name
,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.message
from msdb.dbo.sysjobhistory jh with(nolock)
where jh.job_id=N'375ED3A3-97D5-4B10-924F-4E82C8F49B45'
and jh.step_id=0 -- stand for job
order by jh.instance_id desc
三，记录Agent Job 的活动和状态
在启动SQL Server Agent时，Agent会新建一个Session，并把Session的ID存储到msdb.dbo.syssessions 中，该表记录的信息是Agent 创建的Session ID和Agent启动的时间。注意，只有在Agent关闭后，Agent重新启动时，才会新建Session。在Agent正常运行时，Agent运行Job是不会新建Session的。该Session ID是一个从1开始依次递增的整数，当前Agent可以用最大的SessionID来表示。
每当Agent执行一个job时，Agent都会把Session ID 和Job ID 写入到表 msdb.dbo.sysjobactivity 中，因此 msdb.dbo.sysjobactivity 记录当前Agent 正在运行的每一个Job的信息（Job开始执行的时间，执行成功的最后一个StepID....)，如果要查看Agent当前执行的所有Job，那么 msdb.dbo.sysjobactivity的Session ID必须是当前Agent使用的SessionID，即表msdb.dbo.syssessions中最大的SessionID。
通过 msdb.dbo.sysjobactivity 查看当前Agent Job的活动和状态，关键字段：
- session_id：Agent创建的Session ID
select j.job_id
,j.name as job_name
,iif( a.start_execution_date is not null and a.stop_execution_date is null, 'running' ,'not running') as run_status
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobactivity a
on j.job_id = a.job_id
where session_id = ( select max(session_id) from msdb.dbo.sysjobactivity);
参考文档：