SQL Server 作业监控

最新推荐文章于 2023-10-26 14:42:43 发布

Jerry___Zhang

最新推荐文章于 2023-10-26 14:42:43 发布

阅读量618

点赞数

分类专栏： sql server

本文链接：https://blog.csdn.net/jerry___zhang/article/details/14126931

版权

本文详细介绍了SQL Server Agent Jobs的监控与管理，包括msdb数据库的角色、作业的设置和配置信息、作业执行信息、作业步骤的设置和执行信息以及作业计划。通过SQL查询，展示了如何获取和理解作业的相关信息，如作业状态、执行历史、步骤详情和调度信息。

摘要由CSDN通过智能技术生成

在讲解SQLServer Agent Jobs之前，先要讲解msdb。
Msdb是SQLServer的系统数据库之一，用于存储SQLServer的配置、元数据等信息。包括：
l SQLServer Agent Jobs,Job Steps,Job schedules,Alerts,Operators,等等。
l Service Broker,Log Shipping,Backups/restore信息,维护计划、数据库邮件、基于策略管理信息等等。
l SSIS包。
在这部分，主要集中在msdb的以下部分：
l Job setup/configuration Information
l Job Execution Information
l Job Step(s) Setup/Configuration Information
l Job Step(s) Execution Information
l Schedule Information
————————————————————————————————————————————————————————————————————————————

SQLServer 允许在Replication,SSIS,存储过程,批处理上创建和执行各种自动化任务。允许使用GUI 或者T-SQL脚本创建。这些信息存储在msdb中。SQL Server Agent Job Setup andConfiguration Information：
可以在SSMS中执行以下脚本查找作业信息：

SELECT [sJOB].[job_id] AS [作业ID] ,
        [sJOB].[name] AS [作业名称] ,
        [sDBP].[name] AS [作业创建者] ,
        [sCAT].[name] AS [作业种类] ,
        [sJOB].[description] AS [作业描述] ,
        CASE [sJOB].[enabled]
          WHEN 1 THEN '已启用'
          WHEN 0 THEN '未启用'
        END AS [是否启用] ,--
        [sJOB].[date_created] AS [作业创建日期] ,
        [sJOB].[date_modified] AS [作业最后修改日期] ,
        [sSVR].[name] AS [作业运行服务器] ,
        [sJSTP].[step_id] AS [作业起始步骤] ,
        [sJSTP].[step_name] AS [步骤名称] ,
        CASE WHEN [sSCH].[schedule_uid] IS NULL THEN '否'
             ELSE '是'
        END AS [是否分布式作业] ,
        [sSCH].[schedule_uid] AS [分布式作业ID] ,
        [sSCH].[name] AS [用户定义名称] ,
        CASE [sJOB].[delete_level]
          WHEN 0 THEN '不删除'
          WHEN 1 THEN '成功后删除'
          WHEN 2 THEN '失败后删除'
          WHEN 3 THEN '完成时删除'
        END AS [完成时删除作业级别]
FROM    [msdb].[dbo].[sysjobs] AS [sJOB]
        LEFT JOIN [msdb].[sys].[servers] AS [sSVR] ON [sJOB].[originating_server_id] = [sSVR].[server_id]
        LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
        LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP] ON [sJOB].[job_id] = [sJSTP].[job_id]
                                                           AND [sJOB].[start_step_id] = [sJSTP].[step_id]
        LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
        LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
        LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [作业名称]

SQL Server Agent Job Execution Information：
SQLServer同时存放作业执行信息在msdb中。可以执行以下脚本查询作业执行情况：

SELECT [sJOB].[job_id] AS [作业ID] ,
        [sJOB].[name] AS [作业名] ,
        CASE WHEN [sJOBH].[run_date] IS NULL
                  OR [sJOBH].[run_time] IS NULL THEN NULL
             ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ' '
                  + STUFF(STUFF(RIGHT('000000'
                                      + CAST([sJOBH].[run_time] AS VARCHAR(6)),
                                      6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
        END AS [最近执行时间] ,
        CASE [sJOBH].[run_status]
          WHEN 0 THEN '失败'
          WHEN 1 THEN '成功'
          WHEN 2 THEN '重试'
          WHEN 3 THEN '取消'
          WHEN 4 THEN '正在运行' -- In Progress