查看SQLServer 代理作业的历史信息-mysql チュートリアル-php.cn

查看SQLServer 代理作业的历史信息

WBOY

リリース： 2016-06-07 15:18:47

オリジナル

1329 人が閲覧しました

不敢说众所周知，但是大部分人都应该知道SQLServer的代理作业情况都存储在SQLServer5大系统数据库（master/msdb/model/tempdb/resources）中的MSDB中，而由于代理作业的长期运行和种类较多，所以一般可以看到msdb的大小往往比其他库加起来还大。本文主

不敢说众所周知，但是大部分人都应该知道SQLServer的代理作业情况都存储在SQLServer5大系统数据库（master/msdb/model/tempdb/resources）中的MSDB中，而由于代理作业的长期运行和种类较多，所以一般可以看到msdb的大小往往比其他库加起来还大。本文主要专注在如何查询作业的运行时间点及运行持续时间上。

作为DBA，周期性检查作业情况是一下非常重要的任务。本文不讲述太深入。只讲述如何查询作业的历史运行情况。并加入一下在联机丛书上没有提及，也就是所谓的未公开的系统函数。

作业执行的历史信息存放在msdb.dbo.sysjobhistory中。但是在这个表里面，日期和时间列的显式方式会有点不常规，这就引出了本文的意图。首先我们来看看表里的数据，这里需要关联一下sysjobs表：

SELECT  j.name AS 'JobName' ,
          run_date ,
          run_time
  FROM    msdb.dbo.sysjobs j
          INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
  WHERE   j.enabled = 1  --Only Enabled Jobs
  ORDER BY JobName ,
          run_date ,
          run_time DESC

ログイン後にコピー

运行上面的代码，得到以下的结果：

查看SQLServer 代理作业的历史信息

可以看到run_date这列，虽然能看得懂，但是是YYYYMMDD这样的格式，用起来可能有点不方便。而run_time就更加难用了。Run_time中的180002意味着：18:00:02执行。这些不直观的数据对时常需要使用的DBA来说是一种痛苦，当然，可以通过字符串函数来转换成自己喜欢看的格式。但是这里提供一个微软未公开的函数：

MSDB.dbo.agent_datetime(run_date,run_time)

它会返回一个比较常规的日期格式，使得使用和查看的时候都很方便，作为一个未公开的函数，对其的了解不多只需要会用就可以了。可以使用下面的例子：

SELECT  j.name AS 'JobName' ,
         run_date ,
         run_time ,
         msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime'
 FROM    msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
 WHERE   j.enabled = 1  --Only Enabled Jobs
 ORDER BY JobName ,
         RunDateTime DESC

ログイン後にコピー

结果转换后，得到下面的结果：

查看SQLServer 代理作业的历史信息

可以看到经过函数格式化之后，数据已经很直观了。特别注意，这个未公开函数是从2005以后才引入，2000是没有的。只能通过字符串处理来获得同样的效果。

现在再来看看另外一列，run_duration，运行持续时间，同样，这列是int类型，也和run_time一样，不直观。

SELECT  j.name AS 'JobName' ,
         run_date ,
         run_time ,
         msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime' ,
         run_duration
 FROM    msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
 WHERE   j.enabled = 1  --Only Enabled Jobs
 ORDER BY JobName ,
         RunDateTime DESC

ログイン後にコピー

结果如下：

查看SQLServer 代理作业的历史信息

这列两位数代表仅仅是秒，3位数代表秒和分。单纯从这里比较难看出作业的运行时间。对分析不利。比较遗憾的是没有另外的存储过程来转换这列，所以需要自己编写代码，可以用下面的代码来转换：

SELECT  j.name AS 'JobName' ,
         run_date ,
         run_time ,
         msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime' ,
         run_duration ,
         ( ( run_duration / 10000 * 3600 + ( run_duration / 100 ) % 100 * 60
             + run_duration % 100 + 31 ) / 60 ) AS 'RunDurationMinutes'
 FROM    msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
 WHERE   j.enabled = 1  --Only Enabled Jobs
 ORDER BY JobName ,
         RunDateTime DESC

ログイン後にコピー

为了方便展示，这里我筛选了持续时间比较长的几个作业。

查看SQLServer 代理作业的历史信息

对于很多ETL的作业，可能会有很多步骤，下面来把这些步骤也带出来，这就要关联另外一个表msdb.dbo.sysjobsteps：

SELECT  j.name AS 'JobName' ,
         s.step_id AS 'Step' ,
         s.step_name AS 'StepName' ,
         msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime' ,
         ( ( run_duration / 10000 * 3600 + ( run_duration / 100 ) % 100 * 60
             + run_duration % 100 + 31 ) / 60 ) AS 'RunDurationMinutes'
 FROM    msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
         INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id
                                                AND s.step_id = h.step_id
                                                AND h.step_id  0
 WHERE   j.enabled = 1   --Only Enabled Jobs
 ORDER BY JobName ,
         RunDateTime DESC

ログイン後にコピー