原文: SQL Server 作業(yè)監(jiān)控

在講解SQLServer Agent Jobs之前，先要講解msdb。

Msdb是SQLServer的系統(tǒng)數(shù)據(jù)庫(kù)之一，用于存儲(chǔ)SQLServer的配置、元數(shù)據(jù)等信息。包括：

l? SQLServer Agent Jobs,Job Steps,Job schedules,Alerts,Operators,等等。

l? Service Broker,Log Shipping,Backups/restore信息,維護(hù)計(jì)劃、數(shù)據(jù)庫(kù)郵件、基于策略管理信息等等。

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,存儲(chǔ)過(guò)程,批處理上創(chuàng)建和執(zhí)行各種自動(dòng)化任務(wù)。允許使用GUI 或者T-SQL腳本創(chuàng)建。這些信息存儲(chǔ)在msdb中。SQL Server Agent Job Setup andConfiguration Information：

可以在SSMS中執(zhí)行以下腳本查找作業(yè)信息：

?

    SELECT  [sJOB].[job_id] AS [作業(yè)ID] ,

        [sJOB].[name] AS [作業(yè)名稱] ,

        [sDBP].[name] AS [作業(yè)創(chuàng)建者] ,

        [sCAT].[name] AS [作業(yè)種類] ,

        [sJOB].[description] AS [作業(yè)描述] ,

        CASE [sJOB].[enabled]

          WHEN 1 THEN '已啟用'

          WHEN 0 THEN '未啟用'

        END AS [是否啟用] ,--

        [sJOB].[date_created] AS [作業(yè)創(chuàng)建日期] ,

        [sJOB].[date_modified] AS [作業(yè)最后修改日期] ,

        [sSVR].[name] AS [作業(yè)運(yùn)行服務(wù)器] ,

        [sJSTP].[step_id] AS [作業(yè)起始步驟] ,

        [sJSTP].[step_name] AS [步驟名稱] ,

        CASE WHEN [sSCH].[schedule_uid] IS NULL THEN '否'

             ELSE '是'

        END AS [是否分布式作業(yè)] ,

        [sSCH].[schedule_uid] AS [分布式作業(yè)ID] ,

        [sSCH].[name] AS [用戶定義名稱] ,

        CASE [sJOB].[delete_level]

          WHEN 0 THEN '不刪除'

          WHEN 1 THEN '成功后刪除'

          WHEN 2 THEN '失敗后刪除'

          WHEN 3 THEN '完成時(shí)刪除'

        END AS [完成時(shí)刪除作業(yè)級(jí)別]

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 [作業(yè)名稱]


  

?

SQL Server Agent Job Execution Information：

SQLServer同時(shí)存放作業(yè)執(zhí)行信息在msdb中?？梢詧?zhí)行以下腳本查詢作業(yè)執(zhí)行情況：

?

    SELECT  [sJOB].[job_id] AS [作業(yè)ID] ,

        [sJOB].[name] AS [作業(yè)名] ,

        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 [最近執(zhí)行時(shí)間] ,

        CASE [sJOBH].[run_status]

          WHEN 0 THEN '失敗'

          WHEN 1 THEN '成功'

          WHEN 2 THEN '重試'

          WHEN 3 THEN '取消'

          WHEN 4 THEN '正在運(yùn)行' -- In Progress

        END AS [最近執(zhí)行狀態(tài)] ,

        STUFF(STUFF(RIGHT('000000'

                          + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3,

                    0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)] ,

        [sJOBH].[message] AS [最近運(yùn)行狀態(tài)信息] ,

        CASE [sJOBSCH].[NextRunDate]

          WHEN 0 THEN NULL

          ELSE CAST(CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) + ' '

               + STUFF(STUFF(RIGHT('000000'

                                   + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),

                                   6), 3, 0, ':'), 6, 0, ':') AS DATETIME)

        END AS [下次運(yùn)行時(shí)間]

FROM    [msdb].[dbo].[sysjobs] AS [sJOB]

        LEFT JOIN ( SELECT  [job_id] ,

                            MIN([next_run_date]) AS [NextRunDate] ,

                            MIN([next_run_time]) AS [NextRunTime]

                    FROM    [msdb].[dbo].[sysjobschedules]

                    GROUP BY [job_id]

                  ) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]

        LEFT JOIN ( SELECT  [job_id] ,

                            [run_date] ,

                            [run_time] ,

                            [run_status] ,

                            [run_duration] ,

                            [message] ,

                            ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber

                    FROM    [msdb].[dbo].[sysjobhistory]

                    WHERE   [step_id] = 0

                  ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id]

                                  AND [sJOBH].[RowNumber] = 1

ORDER BY [作業(yè)名]


  

?

SQL Server Anget Job Steps Setup andconfiguration Information：

在作業(yè)系統(tǒng)中，一個(gè)作業(yè)是有層級(jí)的，可以包含一個(gè)或多個(gè)步驟。

運(yùn)行以下腳本查看作業(yè)步驟信息：

?

    SELECT  [sJOB].[job_id] AS [作業(yè)ID] ,

        [sJOB].[name] AS [作業(yè)名] ,

        [sJSTP].[step_uid] AS [步驟ID] ,

        [sJSTP].[step_id] AS [步驟序號(hào)] ,

        [sJSTP].[step_name] AS [步驟名] ,

        CASE [sJSTP].[subsystem]

          WHEN 'ActiveScripting' THEN 'ActiveX Script'

          WHEN 'CmdExec' THEN 'Operating system (CmdExec)'

          WHEN 'PowerShell' THEN 'PowerShell'

          WHEN 'Distribution' THEN 'Replication Distributor'

          WHEN 'Merge' THEN 'Replication Merge'

          WHEN 'QueueReader' THEN 'Replication Queue Reader'

          WHEN 'Snapshot' THEN 'Replication Snapshot'

          WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'

          WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'

          WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'

          WHEN 'SSIS' THEN 'SQL Server Integration Services Package'

          WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'

          ELSE sJSTP.subsystem

        END AS [作業(yè)子系統(tǒng)類型] ,

        [sPROX].[name] AS [作業(yè)運(yùn)行賬號(hào)] ,

        [sJSTP].[database_name] AS [執(zhí)行數(shù)據(jù)庫(kù)名] ,

        [sJSTP].[command] AS [執(zhí)行命令] ,

        CASE [sJSTP].[on_success_action]

          WHEN 1 THEN 'Quit the job reporting success'

          WHEN 2 THEN 'Quit the job reporting failure'

          WHEN 3 THEN 'Go to the next step'

          WHEN 4

          THEN 'Go to Step: '

               + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))

               + ' ' + [sOSSTP].[step_name]

        END AS [執(zhí)行成功后反應(yīng)] ,

        [sJSTP].[retry_attempts] AS [失敗時(shí)的重試次數(shù)] ,

        [sJSTP].[retry_interval] AS [重試間的等待時(shí)間 (Minutes)] ,

        CASE [sJSTP].[on_fail_action]

          WHEN 1 THEN 'Quit the job reporting success'

          WHEN 2 THEN 'Quit the job reporting failure'

          WHEN 3 THEN 'Go to the next step'

          WHEN 4

          THEN 'Go to Step: '

               + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))

               + ' ' + [sOFSTP].[step_name]

        END AS [執(zhí)行失敗后反映]

FROM    [msdb].[dbo].[sysjobsteps] AS [sJSTP]

        INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]

        LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] ON [sJSTP].[job_id] = [sOSSTP].[job_id]

                                                            AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]

        LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] ON [sJSTP].[job_id] = [sOFSTP].[job_id]

                                                            AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]

        LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]

ORDER BY [作業(yè)名] ,

        [步驟序號(hào)]


  

?

SQL Server Anget Job Steps ExecutionInformation：

在msdb中同樣存儲(chǔ)了步驟的執(zhí)行計(jì)劃，執(zhí)行以下語(yǔ)句檢查：

?

?

    SELECT  [sJOB].[job_id] AS [作業(yè)ID] ,

        [sJOB].[name] AS [作業(yè)名稱] ,

        [sJSTP].[step_uid] AS [步驟ID] ,

        [sJSTP].[step_id] AS [步驟序號(hào)] ,

        [sJSTP].[step_name] AS [步驟名稱] ,

        CASE [sJSTP].[last_run_outcome]

          WHEN 0 THEN '失敗'

          WHEN 1 THEN '成功'

          WHEN 2 THEN '重試'

          WHEN 3 THEN '取消'

          WHEN 5 THEN '未知'

        END AS [上次運(yùn)行狀態(tài)] ,

        STUFF(STUFF(RIGHT('000000'

                          + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)), 6),

                    3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)] ,

        [sJSTP].[last_run_retries] AS [上次重試次數(shù)] ,

        CASE [sJSTP].[last_run_date]

          WHEN 0 THEN NULL

          ELSE CAST(CAST([sJSTP].[last_run_date] AS CHAR(8)) + ' '

               + STUFF(STUFF(RIGHT('000000'

                                   + CAST([sJSTP].[last_run_time] AS VARCHAR(6)),

                                   6), 3, 0, ':'), 6, 0, ':') AS DATETIME)

        END AS [上次運(yùn)行時(shí)間]

FROM    [msdb].[dbo].[sysjobsteps] AS [sJSTP]

        INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]

ORDER BY [作業(yè)名稱] ,

        [步驟序號(hào)]


  

?

SQL Server Agent Job Sechdule Information：

SQLServer允許在特定時(shí)間創(chuàng)建各種計(jì)劃，每個(gè)計(jì)劃能組合成一個(gè)或多個(gè)SQLServer Agent Jobs。執(zhí)行以下腳本查詢情況：

    SELECT  [schedule_uid] AS [作業(yè)計(jì)劃ID] ,

        [name] AS [作業(yè)計(jì)劃名稱] ,

        CASE [enabled]

          WHEN 1 THEN '已啟用'

          WHEN 0 THEN '未啟用'

        END AS [是否啟用] ,

        CASE WHEN [freq_type] = 64

             THEN 'Start automatically when SQL Server Agent starts'

             WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'

             WHEN [freq_type] IN ( 4, 8, 16, 32 ) THEN 'Recurring'

             WHEN [freq_type] = 1 THEN 'One Time'

        END [作業(yè)計(jì)劃類型] ,

        CASE [freq_type]

          WHEN 1 THEN 'One Time'

          WHEN 4 THEN 'Daily'

          WHEN 8 THEN 'Weekly'

          WHEN 16 THEN 'Monthly'

          WHEN 32 THEN 'Monthly - Relative to Frequency Interval'

          WHEN 64 THEN 'Start automatically when SQL Server Agent starts'

          WHEN 128 THEN 'Start whenever the CPUs become idle'

        END [作業(yè)運(yùn)行頻率] ,

        CASE [freq_type]

          WHEN 4

          THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3))

               + ' day(s)'

          WHEN 8

          THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))

               + ' week(s) on '

               + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday'

                      ELSE ''

                 END + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday'

                            ELSE ''

                       END

               + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday'

                      ELSE ''

                 END + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday'

                            ELSE ''

                       END

               + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday'

                      ELSE ''

                 END + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday'

                            ELSE ''

                       END

               + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday'

                      ELSE ''

                 END

          WHEN 16

          THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))

               + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))

               + ' month(s)'

          WHEN 32

          THEN 'Occurs on ' + CASE [freq_relative_interval]

                                WHEN 1 THEN 'First'

                                WHEN 2 THEN 'Second'

                                WHEN 4 THEN 'Third'

                                WHEN 8 THEN 'Fourth'

                                WHEN 16 THEN 'Last'

                              END + ' ' + CASE [freq_interval]

                                            WHEN 1 THEN 'Sunday'

                                            WHEN 2 THEN 'Monday'

                                            WHEN 3 THEN 'Tuesday'

                                            WHEN 4 THEN 'Wednesday'

                                            WHEN 5 THEN 'Thursday'

                                            WHEN 6 THEN 'Friday'

                                            WHEN 7 THEN 'Saturday'

                                            WHEN 8 THEN 'Day'

                                            WHEN 9 THEN 'Weekday'

                                            WHEN 10 THEN 'Weekend day'

                                          END + ' of every '

               + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'

        END AS [循環(huán)間隔] ,

        CASE [freq_subday_type]

          WHEN 1

          THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000'

                                                     + CAST([active_start_time] AS VARCHAR(6)),

                                                     6), 3, 0, ':'), 6, 0, ':')

          WHEN 2

          THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3))

               + ' Second(s) between ' + STUFF(STUFF(RIGHT('000000'

                                                           + CAST([active_start_time] AS VARCHAR(6)),

                                                           6), 3, 0, ':'), 6,

                                               0, ':') + ' & '

               + STUFF(STUFF(RIGHT('000000'

                                   + CAST([active_end_time] AS VARCHAR(6)), 6),

                             3, 0, ':'), 6, 0, ':')

          WHEN 4

          THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3))

               + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000'

                                                           + CAST([active_start_time] AS VARCHAR(6)),

                                                           6), 3, 0, ':'), 6,

                                               0, ':') + ' & '

               + STUFF(STUFF(RIGHT('000000'

                                   + CAST([active_end_time] AS VARCHAR(6)), 6),

                             3, 0, ':'), 6, 0, ':')

          WHEN 8

          THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3))

               + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000'

                                                         + CAST([active_start_time] AS VARCHAR(6)),

                                                         6), 3, 0, ':'), 6, 0,

                                             ':') + ' & '

               + STUFF(STUFF(RIGHT('000000'

                                   + CAST([active_end_time] AS VARCHAR(6)), 6),

                             3, 0, ':'), 6, 0, ':')

        END [計(jì)劃運(yùn)行頻率] ,

        STUFF(STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0,

              '-') AS [作業(yè)啟用開(kāi)始時(shí)間] ,

        STUFF(STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0,

              '-') AS [作業(yè)啟用結(jié)束時(shí)間] ,

        [date_created] AS [作業(yè)創(chuàng)建日期] ,

        [date_modified] AS [作業(yè)上次修改日期]

FROM    [msdb].[dbo].[sysschedules]

ORDER BY [作業(yè)計(jì)劃名稱]


  

?

SQL Server 作業(yè)監(jiān)控