你的位置:首页 > 数据库

[数据库]查看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_modifiedfrom 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_durationfrom msdb.dbo.sysjobhistory jh with(nolock)where job_id=N'07A53839-E012-4C80-9227-15594165B013'order by instance_id desc

3,Job History的查询

use msdbgo--查看job 最后一次执行的情况DECLARE @Job_ID uniqueidentifier;select @Job_ID=j.job_idfrom 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 ridfrom 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 Durationfrom msdb.dbo.sysjobs j with(nolock)inner join msdb.dbo.sysjobhistory jh with(nolock)  on jh.job_id=j.job_idinner 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>0where c.rid=1order 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_retriesfrom msdb.dbo.sysjobsteps js with(nolock)where js.job_id=N'DF0C68ED-7C76-4571-A72D-CD6161EFFC04'

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

use msdbGO;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 ridfrom msdb.dbo.sysjobs j with(nolock)inner join msdb.dbo.sysjobhistory jh with(nolock)  on j.job_id=jh.job_idwhere 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 StepMessagefrom cte_job jouter apply(select top 1 jh.step_id,jh.step_name,jh.run_status,jh.run_date,jh.run_time,jh.run_duration,jh.messagefrom 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_timeorder by jh.step_id desc) as jhwhere j.Rid=1 --Last Execution  and j.run_status in(0,2) --0 = Failed, 2=retryorder 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_nameFROM msdb.dbo.sysjobactivity ja with(nolock)LEFT JOIN msdb.dbo.sysjobhistory jh with(nolock)  ON ja.job_history_id = jh.instance_idJOIN msdb.dbo.sysjobs j   ON ja.job_id = j.job_idJOIN msdb.dbo.sysjobsteps js  ON ja.job_id = js.job_id  AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_idWHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)AND start_execution_date is not nullAND 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)