SQL SERVER 中如何用脚本管理作业-白红宇

SQL SERVER 中如何用脚本管理作业

阅读量：5067 次

发布时间：2019-06-12

本文共 24212 字，大约阅读时间需要 80 分钟。

在SQL SERVER中用脚本管理作业，在绝大部分场景下，脚本都比UI界面管理作业要高效、简洁。打个简单的比方，如果你要查看作业的运行时长，如果用UI界面查看，100个作业，你就得在历史记录里面至少查看一百次甚至更多，还要记录、统计作业各个步骤的执行时间。而用脚本，一个查询就OK了。这篇文章分享一些我在数据库管理过程中积累的一些常用脚本。如有不足或需要完善的地方，也请多多指教。

1：业务场景：你想了解一下所有作业的Schedule信息，方便你作出调整或分析。例如作业的执行频率；例如你想查询那些作业是一小时执行一次的，那些是间隔几分钟执行一次的，使用下面脚本来查看吧。

DECLARE @ManyTimes TABLE(    freq_relative_interval INT,    times  NVARCHAR(12))INSERT INTO @ManyTimesSELECT 1  ,N'第一个' UNION ALLSELECT 2  ,N'第二个' UNION ALLSELECT 4  ,N'第三个' UNION ALLSELECT 8  ,N'第四个' UNION ALLSELECT 16 ,N'最后一个' DECLARE @WeekDays TABLE(    freq_interval    INT,    weekdays        NVARCHAR(120) ) INSERT INTO @WeekDaysSELECT 1    ,N'星期日               '  UNION ALLSELECT 2    ,N'星期一               '  UNION ALLSELECT 4    ,N'星期二               '  UNION ALLSELECT 8    ,N'星期三               '  UNION ALLSELECT 16   ,N'星期四               '  UNION ALLSELECT 32   ,N'星期五               '  UNION ALLSELECT 64   ,N'星期六               '  UNION ALLSELECT 3    ,N'星期日,一            '  UNION ALLSELECT 5    ,N'星期日,二            '  UNION ALLSELECT 9    ,N'星期日,三            '  UNION ALLSELECT 17   ,N'星期日,四            '  UNION ALLSELECT 33   ,N'星期日,五            '  UNION ALLSELECT 65   ,N'星期日,六            '  UNION ALLSELECT 6    ,N'星期一,二            '  UNION ALLSELECT 10   ,N'星期一,三            '  UNION ALLSELECT 18   ,N'星期一,四            '  UNION ALLSELECT 34   ,N'星期一,五            '  UNION ALLSELECT 66   ,N'星期一,六            '  UNION ALLSELECT 12   ,N'星期二,三            '  UNION ALLSELECT 20   ,N'星期二,四            '  UNION ALLSELECT 36   ,N'星期二,五            '  UNION ALLSELECT 68   ,N'星期二,六            '  UNION ALLSELECT 24   ,N'星期三,四            '  UNION ALLSELECT 40   ,N'星期三,五            '  UNION ALLSELECT 72   ,N'星期三,六            '  UNION ALLSELECT 48   ,N'星期四,五            '  UNION ALLSELECT 80   ,N'星期四,六            '  UNION ALLSELECT 96   ,N'星期五,六            '  UNION ALLSELECT 7    ,N'星期日,一,二         '  UNION ALLSELECT 11   ,N'星期日,一,三         '  UNION ALLSELECT 19   ,N'星期日,一,四         '  UNION ALLSELECT 35   ,N'星期日,一,五         '  UNION ALLSELECT 67   ,N'星期日,一,六         '  UNION ALLSELECT 13   ,N'星期日,二,三         '  UNION ALLSELECT 21   ,N'星期日,二,四         '  UNION ALLSELECT 37   ,N'星期日,二,五         '  UNION ALLSELECT 69   ,N'星期日,二,六         '  UNION ALLSELECT 25   ,N'星期日,三,四         '  UNION ALLSELECT 41   ,N'星期日,三,五         '  UNION ALLSELECT 73   ,N'星期日,三,六         '  UNION ALLSELECT 49   ,N'星期日,四,五         '  UNION ALLSELECT 81   ,N'星期日,四,六         '  UNION ALLSELECT 97   ,N'星期日,五,六         '  UNION ALLSELECT 14   ,N'星期一,二,三         '  UNION ALLSELECT 22   ,N'星期一,二,四         '  UNION ALLSELECT 38   ,N'星期一,二,五         '  UNION ALLSELECT 70   ,N'星期一,二,六         '  UNION ALLSELECT 26   ,N'星期一,三,四         '  UNION ALLSELECT 42   ,N'星期一,三,五         '  UNION ALLSELECT 74   ,N'星期一,三,六         '  UNION ALLSELECT 50   ,N'星期一,四,五         '  UNION ALLSELECT 82   ,N'星期一,四,六         '  UNION ALLSELECT 98   ,N'星期一,五,六         '  UNION ALLSELECT 28   ,N'星期二,三,四         '  UNION ALLSELECT 44   ,N'星期二,三,五         '  UNION ALLSELECT 76   ,N'星期二,三,六         '  UNION ALLSELECT 52   ,N'星期二,四,五         '  UNION ALLSELECT 84   ,N'星期二,四,六         '  UNION ALLSELECT 100  ,N'星期二,五,六         '  UNION ALLSELECT 56   ,N'星期三,四,五         '  UNION ALLSELECT 88   ,N'星期三,四,六         '  UNION ALLSELECT 104  ,N'星期三,五,六         '  UNION ALLSELECT 112  ,N'星期四,五,六         '  UNION ALLSELECT 15   ,N'星期日,一,二,三      '  UNION ALLSELECT 23   ,N'星期日,一,二,四      '  UNION ALLSELECT 39   ,N'星期日,一,二,五      '  UNION ALLSELECT 71   ,N'星期日,一,二,六      '  UNION ALLSELECT 27   ,N'星期日,一,三,四      '  UNION ALLSELECT 43   ,N'星期日,一,三,五      '  UNION ALLSELECT 75   ,N'星期日,一,三,六      '  UNION ALLSELECT 51   ,N'星期日,一,四,五      '  UNION ALLSELECT 83   ,N'星期日,一,四,六      '  UNION ALLSELECT 99   ,N'星期日,一,五,六      '  UNION ALLSELECT 29   ,N'星期日,二,三,四      '  UNION ALLSELECT 45   ,N'星期日,二,三,五      '  UNION ALLSELECT 77   ,N'星期日,二,三,六      '  UNION ALLSELECT 53   ,N'星期日,二,四,五      '  UNION ALLSELECT 85   ,N'星期日,二,四,六      '  UNION ALLSELECT 101  ,N'星期日,二,五,六      '  UNION ALLSELECT 57   ,N'星期日,三,四,五      '  UNION ALLSELECT 89   ,N'星期日,三,四,六      '  UNION ALLSELECT 105  ,N'星期日,三,五,六      '  UNION ALLSELECT 113  ,N'星期日,四,五,六      '  UNION ALLSELECT 30   ,N'星期一,二,三,四      '  UNION ALLSELECT 46   ,N'星期一,二,三,五      '  UNION ALLSELECT 78   ,N'星期一,二,三,六      '  UNION ALLSELECT 54   ,N'星期一,二,四,五      '  UNION ALLSELECT 86   ,N'星期一,二,四,六      '  UNION ALLSELECT 102  ,N'星期一,二,五,六      '  UNION ALLSELECT 58   ,N'星期一,三,四,五      '  UNION ALLSELECT 90   ,N'星期一,三,四,六      '  UNION ALLSELECT 106  ,N'星期一,三,五,六      '  UNION ALLSELECT 114  ,N'星期一,四,五,六      '  UNION ALLSELECT 60   ,N'星期二,三,四,五      '  UNION ALLSELECT 92   ,N'星期二,三,四,六      '  UNION ALLSELECT 108  ,N'星期二,三,五,六      '  UNION ALLSELECT 116  ,N'星期二,四,五,六      '  UNION ALLSELECT 120  ,N'星期三,四,五,六      '  UNION ALLSELECT 31   ,N'星期日,一,二,三,四   '  UNION ALLSELECT 47   ,N'星期日,一,二,三,五   '  UNION ALLSELECT 79   ,N'星期日,一,二,三,六   '  UNION ALLSELECT 55   ,N'星期日,一,二,四,五   '  UNION ALLSELECT 87   ,N'星期日,一,二,四,六   '  UNION ALLSELECT 103  ,N'星期日,一,二,五,六   '  UNION ALLSELECT 59   ,N'星期日,一,三,四,五   '  UNION ALLSELECT 91   ,N'星期日,一,三,四,六   '  UNION ALLSELECT 107  ,N'星期日,一,三,五,六   '  UNION ALLSELECT 115  ,N'星期日,一,四,五,六   '  UNION ALLSELECT 61   ,N'星期日,二,三,四,五   '  UNION ALLSELECT 93   ,N'星期日,二,三,四,六   '  UNION ALLSELECT 109  ,N'星期日,二,三,五,六   '  UNION ALLSELECT 117  ,N'星期日,二,四,五,六   '  UNION ALLSELECT 121  ,N'星期日,三,四,五,六   '  UNION ALLSELECT 62   ,N'星期一,二,三,四,五   '  UNION ALLSELECT 94   ,N'星期一,二,三,四,六   '  UNION ALLSELECT 110  ,N'星期一,二,三,五,六   '  UNION ALLSELECT 118  ,N'星期一,二,四,五,六   '  UNION ALLSELECT 122  ,N'星期一,三,四,五,六   '  UNION ALLSELECT 124  ,N'星期二,三,四,五,六   '  UNION ALLSELECT 63   ,N'星期日,一,二,三,四,五'  UNION ALLSELECT 95   ,N'星期日,一,二,三,四,六'  UNION ALLSELECT 111  ,N'星期日,一,二,三,五,六'  UNION ALLSELECT 119  ,N'星期日,一,二,四,五,六'  UNION ALLSELECT 123  ,N'星期日,一,三,四,五,六'  UNION ALLSELECT 125  ,N'星期日,二,三,四,五,六'  UNION ALLSELECT 126  ,N'星期一,二,三,四,五,六'  UNION ALLSELECT 127  ,N'星期日,一,二,三,四,五,六';       SELECT  d.job_id                                    AS job_id    ,        j.name                                        AS job_name ,        CASE WHEN j.enabled =1  THEN N'启用'              ELSE N'禁用' END                        AS job_staus,        CASE WHEN freq_type=1    THEN N'运行一次'             WHEN freq_type=4    THEN N'每天执行'             WHEN freq_type=8    THEN N'每周执行'             WHEN freq_type=16    THEN N'每月执行'             WHEN freq_type=32    THEN N'每月执行'             WHEN freq_type=64    THEN N'代理服务启动时运行'             WHEN freq_type=128 THEN N'在计算机空闲时运行'        END                                            AS  freq_type,        CASE WHEN freq_type=1    THEN  N'选项无意义'             WHEN freq_type=4    THEN                 (                 CASE WHEN freq_subday_type=4                           THEN  N'每隔' +  CONVERT(NVARCHAR(4),freq_subday_interval) +N'分钟执行一次'                  WHEN freq_subday_type=8                       THEN  N'每隔' +  CONVERT(NVARCHAR(4),freq_subday_interval) +N'小时执行一次'                 ELSE         N'每天执行'+ CONVERT(NVARCHAR(4),freq_interval)  + N'次'                  END                 )             WHEN freq_type=8    THEN                 ( SELECT w.weekdays FROM   @WeekDays w WHERE w.freq_interval= s.freq_interval                 )                                                 WHEN freq_type=16  THEN  N'每月' +  CONVERT(NVARCHAR(4),freq_interval)     + N'号执行'             WHEN freq_type=32  THEN                (CASE WHEN s.freq_relative_interval = 0 THEN   N'每月星期' + CONVERT(NVARCHAR(4),freq_interval)  + N'执行'                      WHEN s.freq_relative_interval = 1 THEN                           ( CASE WHEN s.freq_interval =8  THEN   N'每月第一天执行'                                    ELSE N'每月' + (SELECT times FROM @ManyTimes                                                   WHERE freq_relative_interval=s.freq_relative_interval)                                      + N'星期'                                      + CONVERT(NVARCHAR(2),s.freq_interval -1)                           END                           )                      WHEN s.freq_relative_interval = 2 THEN                             (CASE WHEN s.freq_interval =8  THEN   N'每月第二天执行'                                    ELSE N'每月'+ (SELECT times FROM @ManyTimes                                                    WHERE freq_relative_interval=s.freq_relative_interval)                                       + N'星期'                                       + CONVERT(NVARCHAR(2),s.freq_interval -1)                              END                             )                      WHEN s.freq_relative_interval = 4 THEN                             (CASE WHEN s.freq_interval =8  THEN   N'每月第三天执行'                                 ELSE N'每月' + (SELECT times FROM @ManyTimes                                                 WHERE freq_relative_interval=s.freq_relative_interval)                                                                                     + N'星期'                                 + CONVERT(NVARCHAR(2),s.freq_interval -1)                             END                             )                      WHEN s.freq_relative_interval = 8 THEN                              (CASE WHEN   s.freq_interval =8  THEN N'每月第四天执行'                                  ELSE N'每月' + (SELECT times FROM @ManyTimes                                                  WHERE freq_relative_interval=s.freq_relative_interval)                                    + N'星期'                              + CONVERT(NVARCHAR(2),s.freq_interval -1)                             END                             )                      WHEN s.freq_relative_interval =16 THEN                              (CASE WHEN   s.freq_interval =8  THEN   N'每月最后一天执行'                                 ELSE N'每月' + (SELECT times FROM @ManyTimes                                                  WHERE freq_relative_interval=s.freq_relative_interval)                                    + N'星期'                              + CONVERT(NVARCHAR(2),s.freq_interval -1)                              END                             )                END )        END AS freq_relative_interval,        CASE WHEN freq_subday_type =1 THEN N'指定时间点执行一次'              WHEN freq_subday_type =2 THEN N'每隔：' + CAST(freq_subday_interval AS VARCHAR(2)) + N'秒执行一次'             WHEN freq_subday_type =4 THEN N'每隔：' + CAST(freq_subday_interval AS VARCHAR(2)) + N'分执行一次'             WHEN freq_subday_type =8 THEN N'每隔：' + CAST(freq_subday_interval AS VARCHAR(2)) + N'小时执行一次'        END AS freq_subday_type,         CASE WHEN freq_subday_type =1 THEN N'开始时间点：'                     + CAST(active_start_time / 10000   AS VARCHAR(2)) + N'点'                    + CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'             WHEN freq_subday_type =2 THEN N'开始时间点：'                     + CAST(active_start_time / 10000 AS VARCHAR(2)) + N'点'                    + CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'             WHEN freq_subday_type =4 THEN N'开始时间点：'                     + CAST(active_start_time / 10000 AS VARCHAR(2))   + N'点'                    + CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'             WHEN freq_subday_type =8 THEN N'开始时间点：'                    + CAST(active_start_time / 10000 AS VARCHAR(2))   + N'点'                    + CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'        END AS job_start_time,         CASE WHEN freq_subday_type =1 THEN N'结束时间点：'                     + CAST(active_end_time / 10000   AS VARCHAR(2)) + N'点'                    + CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'             WHEN freq_subday_type =2 THEN N'结束时间点：'                     + CAST(active_end_time / 10000 AS VARCHAR(2)) + N'点'                    + CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'             WHEN freq_subday_type =4 THEN N'结束时间点：'                     + CAST(active_end_time / 10000 AS VARCHAR(2))   + N'点'                    + CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'             WHEN freq_subday_type =8 THEN N'结束时间点：'                    + CAST(active_end_time / 10000 AS VARCHAR(2))   + N'点'                    + CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'        END AS job_end_time,         freq_type,        freq_interval,             freq_subday_type,        freq_subday_interval,        active_start_date,        active_start_timeFROM msdb.dbo.sysschedules sINNER JOIN msdb.dbo.sysjobschedules d ON s.schedule_id=d.schedule_idINNER JOIN msdb.dbo.sysjobs j ON d.job_id = j.job_idORDER BY j.name

如下测试案例，非常的清晰明了，一目了然。

2：业务场景：你想查看这个数据库实例有多少作业。

SELECT j.job_id                                         AS JOB_ID                  ,j.name                                         AS JOB_NAME                ,CASE WHEN [enabled] =1 THEN 'Enabled'                              ELSE 'Disabled'  END   AS JOB_ENABLED          ,l.name                                         AS JOB_OWNER         ,j.category_id                                 AS JOB_CATEGORY_ID      ,c.name                                         AS JOB_CATEGORY_NAME      ,[description]                                 AS JOB_DESCRIPTION         ,date_created                                     AS DATE_CREATED            ,date_modified                                 AS DATE_MODIFIEDFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_idINNER JOIN sys.syslogins l ON l.sid = j.owner_sidORDER BY j.name

在这个SQL上可以引申出跟多的SQL，例如查询某段时间内创建的作业等等

3：业务场景：你想查看、统计哪些作业被禁止了。

--查看实例下被禁用的作业SELECT j.job_id                                         AS JOB_ID                  ,j.name                                           AS JOB_NAME                ,CASE WHEN [enabled] =1 THEN 'Enabled'                              ELSE 'Disabled'  END      AS JOB_ENABLED          ,l.name                                           AS JOB_OWNER         ,j.category_id                                    AS JOB_CATEGORY_ID      ,c.name                                           AS JOB_CATEGORY_NAME      ,[description]                                    AS JOB_DESCRIPTION         ,date_created                                     AS DATE_CREATED            ,date_modified                                    AS DATE_MODIFIEDFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_idINNER JOIN sys.syslogins l ON l.sid = j.owner_sidWHERE j.enabled =0ORDER BY j.name

4：业务场景：你想查看有那些账号在运行那些作业，例如某个同事离职了，但是他的那个NT账号在运行一些作业，你需要修改作业的Owner，否则当系统管理员禁用这个NT账号后，这些作业就会出错。

--查看某个Owner的作业SELECT j.job_id                                         AS JOB_ID                  ,j.name                                           AS JOB_NAME                ,CASE WHEN [enabled] =1 THEN 'Enabled'                              ELSE 'Disabled'  END      AS JOB_ENABLED          ,l.name                                           AS JOB_OWNER         ,j.category_id                                    AS JOB_CATEGORY_ID      ,c.name                                           AS JOB_CATEGORY_NAME      ,[description]                                    AS JOB_DESCRIPTION         ,date_created                                     AS DATE_CREATED            ,date_modified                                    AS DATE_MODIFIEDFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_idINNER JOIN sys.syslogins l ON l.sid = j.owner_sidWHERE l.name='Domain\UserName'ORDER BY j.name

5：业务场景：你需要查看作业的具体步骤，例如作业执行的某个存储过程，或者有个同事想了解作业的运行情况，但是他不记得作业的名称，只知道这个作业执行了某个存储过程，那么就可以在下面SQL的基础上进行扩展。

SELECT j.job_id                                      AS JOB_ID                  ,j.name                                        AS JOB_NAME      ,s.step_id                                     AS Step_Id                ,s.command                                     AS Command      ,CASE WHEN [enabled] =1 THEN 'Enabled'                              ELSE 'Disabled'  END   AS JOB_ENABLED             ,j.category_id                                 AS JOB_CATEGORY_ID      ,c.name                                        AS JOB_CATEGORY_NAME      ,[description]                                 AS JOB_DESCRIPTION         ,date_created                                  AS DATE_CREATED            ,date_modified                                 AS DATE_MODIFIEDFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_idINNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_idORDER BY j.name, s.step_id

--查询那个作业执行了某个存储过程（因为有时候，忘记了作业名称,但是知道那个作业调用了某个存储过程，此时开发人员找你帮忙找到对应的作业,就可以通过下面SQL查找）

SELECT j.job_id                                      AS JOB_ID                  ,j.name                                        AS JOB_NAME      ,s.step_id                                     AS Step_Id                ,s.command                                     AS Command      ,CASE WHEN [enabled] =1 THEN 'Enabled'                              ELSE 'Disabled'  END   AS JOB_ENABLED             ,j.category_id                                 AS JOB_CATEGORY_ID      ,c.name                                        AS JOB_CATEGORY_NAME      ,[description]                                 AS JOB_DESCRIPTION         ,date_created                                  AS DATE_CREATED            ,date_modified                                 AS DATE_MODIFIEDFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_idINNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_idWHERE s.command LIKE '%procedure_name%'

6：业务场景：数据库迁移时，你想把属于这个数据库的作业也迁移走，但是那些作业是在这些数据库上面运行呢？可以通过下面SQL来查找。

---查看属于某个数据库的作业(根据作业脚本执行的数据库判定)

SELECT j.job_id                                      AS JOB_ID                  ,j.name                                        AS JOB_NAME                ,CASE WHEN [enabled] =1 THEN 'Enabled'                              ELSE 'Disabled' END    AS JOB_ENABLED             ,j.category_id                                 AS JOB_CATEGORY_ID      ,c.name                                        AS JOB_CATEGORY_NAME      ,[description]                                 AS JOB_DESCRIPTION         ,date_created                                  AS DATE_CREATED            ,date_modified                                 AS DATE_MODIFIEDFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_idWHERE   job_id IN( SELECT job_id                  FROM    msdb.dbo.sysjobsteps                  WHERE   database_name = 'YourSQLDba' ) ORDER BY j.name

7：业务场景：需要查看那些作业类型为“操作系统（CmdExec)"的作业。

--查看作业类型为“操作系统（CmdExec)"的作业

SELECT j.job_id                                       AS JOB_ID                  ,j.name                                         AS JOB_NAME                ,CASE WHEN [enabled] =1 THEN 'Enabled'                              ELSE 'Disabled' END     AS JOB_ENABLED             ,j.category_id                                  AS JOB_CATEGORY_ID      ,c.name                                         AS JOB_CATEGORY_NAME      ,description                                    AS JOB_DESCRIPTION         ,date_created                                   AS DATE_CREATED            ,date_modified                                  AS DATE_MODIFIEDFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id WHERE EXISTS    (SELECT 1 FROM msdb.dbo.sysjobsteps s WHERE             j.job_id = s.job_id AND s.subsystem='CmdExec')

8：业务场景：需要查看今天或某个时间段内出错的作业。

--查询那些作业在今天出错（如果要查询历史出错作业，去掉查询时间条件即可）

SELECT j.name                AS JOB_NAME              ,j.description         AS JOB_Description       ,j.date_created        AS Date_Created          ,j.date_modified       AS Date_ModifiedFROM  msdb.dbo.sysjobs jWHERE enabled = 1      AND EXISTS (        SELECT  1        FROM    Msdb.dbo.sysjobhistory h        WHERE   run_status = 0            AND j.job_id = h.job_id              AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT) )

---查看某个或所有作业的出错的历史记录信息

SELECT j.name                AS job_name     , h.step_id             AS step_id     , h.step_name           AS job_step_name     , h.sql_severity        AS sql_severity     , h.[message]           AS error_message     , h.run_date            AS run_date     , h.run_time            AS run_time     , h.run_duration        AS run_duration FROM msdb.dbo.sysjobhistory hLEFT JOIN msdb.dbo.sysjobs j ON h.job_id =j.job_id WHERE run_status=0--AND j.job_name =''ORDER BY j.name, h.run_date, h.run_time, h.step_id

9：业务场景：查看作业的执行时间信息。

--查询作业的运行时间，检查作业是否正常运行或存在问题。

SELECT  j.name                        AS Job_Name        ,        h.step_id                     AS Step_Id         ,        h.step_name                   AS Step_Name       ,        h.message                     AS Message         ,        h.run_date                    AS Run_Date        ,        h.run_time                    AS Run_Time        ,        msdb.dbo.agent_datetime(h.run_date, h.run_time)                                     AS 'RunDateTime' ,        CAST(run_duration / 10000 AS VARCHAR(2)) + N'小时'        + CAST(( run_duration - run_duration / 10000 * 10000 ) / 100 AS VARCHAR(2)) + N'分钟'        + SUBSTRING(CAST(run_duration AS VARCHAR(10)),                            LEN(CAST(run_duration AS VARCHAR(10))) - 1, 2)  + N'秒'        AS run_durationFROM    msdb.dbo.sysjobhistory h        LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_idORDER BY Job_Name, h.Step_Id

SELECT  j.name                        AS Job_Name        ,        h.step_id                     AS Step_Id         ,        h.step_name                   AS Step_Name       ,        h.message                     AS Message         ,        h.run_date                    AS Run_Date        ,        h.run_time                    AS Run_Time        ,        msdb.dbo.agent_datetime(h.run_date, h.run_time)                                     AS 'RunDateTime' ,        CAST(run_duration / 10000 AS VARCHAR(2)) + N'小时'        + CAST(( run_duration - run_duration / 10000 * 10000 ) / 100 AS VARCHAR(2)) + N'分钟'        + SUBSTRING(CAST(run_duration AS VARCHAR(10)),                            LEN(CAST(run_duration AS VARCHAR(10))) - 1, 2)  + N'秒'        AS run_durationFROM    msdb.dbo.sysjobhistory h        LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_idWHERE   Run_Date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)  --今天执行的作业    AND h.run_status = 1    --执行成功ORDER BY h.run_duration DESC

10：业务场景：查询作业运行时间超过某个阀值的所有作业。例如作业执行时间超过一分钟的作业

查询今天执行时间大于一分钟的作业

SELECT  j.name                        AS Job_Name        ,        h.step_id                     AS Step_Id         ,        h.step_name                   AS Step_Name       ,        h.message                     AS Message         ,        h.run_date                    AS Run_Date        ,        h.run_time                    AS Run_Time        ,        msdb.dbo.agent_datetime(h.run_date, h.run_time)                                     AS 'RunDateTime' ,        CAST(run_duration / 10000 AS VARCHAR(2)) + N'小时'        + CAST(( run_duration - run_duration / 10000 * 10000 ) / 100 AS VARCHAR(2)) + N'分钟'        + SUBSTRING(CAST(run_duration AS VARCHAR(10)),                            LEN(CAST(run_duration AS VARCHAR(10))) - 1, 2)  + N'秒'        AS run_durationFROM    msdb.dbo.sysjobhistory h        LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_idWHERE   Run_Date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)  --今天执行的作业    AND h.run_status = 1    --执行成功    AND h.run_duration > 100 --大于一分钟的作业ORDER BY Job_Name, h.Step_Id

11：业务场景，查看正在执行的作业。

SELECT job.job_id,       job.name  ,       sa.run_requested_date,       sa.start_execution_dateFROM   msdb.dbo.sysjobs_view job       INNER JOIN msdb.dbo.sysjobactivity sa ON job.job_id = sa.job_id         -- get only the current session       INNER JOIN msdb.dbo.syssessions s ON s.session_id = sa.session_id       INNER JOIN ( SELECT MAX(agent_start_date) AS max_agent_start_date                    FROM   msdb.dbo.syssessions                  ) session_max ON s.agent_start_date = session_max.max_agent_start_dateWHERE  sa.run_requested_date IS NOT NULL       AND sa.stop_execution_date IS NULL;

12：业务场景，查看某个作业的详细信息。

EXEC msdb.dbo.sp_help_job @Job_name = 'YourSQLDba_LogBackups'

转载于:https://www.cnblogs.com/footleg/p/7131501.html

你可能感兴趣的文章