查看Job执行的历史记录
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;
参考文档：
A T-SQL query to get current job activity
