Agent 第一篇：Job 基础

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),' ','0'),5,0,':'),3,0,':') 
         as datetime) as job_start_datetime
    ,stuff(stuff(replace(str(jh.run_duration,6),' ','0'),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);

参考文档：

SQL Server Agent Tables (Transact-SQL)