亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

SQL Server 作業監控

系統 1846 0
原文: SQL Server 作業監控

在講解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

        END AS [最近執行狀態] ,

        STUFF(STUFF(RIGHT('000000'

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

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

        [sJOBH].[message] AS [最近運行狀態信息] ,

        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 [下次運行時間]

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 [作業名]


  

?

SQL Server Anget Job Steps Setup andconfiguration Information:

在作業系統中,一個作業是有層級的,可以包含一個或多個步驟。

運行以下腳本查看作業步驟信息:

?

    SELECT  [sJOB].[job_id] AS [作業ID] ,

        [sJOB].[name] AS [作業名] ,

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

        [sJSTP].[step_id] AS [步驟序號] ,

        [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 [作業子系統類型] ,

        [sPROX].[name] AS [作業運行賬號] ,

        [sJSTP].[database_name] AS [執行數據庫名] ,

        [sJSTP].[command] AS [執行命令] ,

        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 [執行成功后反應] ,

        [sJSTP].[retry_attempts] AS [失敗時的重試次數] ,

        [sJSTP].[retry_interval] AS [重試間的等待時間 (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 [執行失敗后反映]

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 [作業名] ,

        [步驟序號]


  

?

SQL Server Anget Job Steps ExecutionInformation:

在msdb中同樣存儲了步驟的執行計劃,執行以下語句檢查:

?

?

    SELECT  [sJOB].[job_id] AS [作業ID] ,

        [sJOB].[name] AS [作業名稱] ,

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

        [sJSTP].[step_id] AS [步驟序號] ,

        [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 [上次運行狀態] ,

        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 [上次重試次數] ,

        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 [上次運行時間]

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

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

ORDER BY [作業名稱] ,

        [步驟序號]


  

?

SQL Server Agent Job Sechdule Information:

SQLServer允許在特定時間創建各種計劃,每個計劃能組合成一個或多個SQLServer Agent Jobs。執行以下腳本查詢情況:

    SELECT  [schedule_uid] AS [作業計劃ID] ,

        [name] AS [作業計劃名稱] ,

        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 [作業計劃類型] ,

        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 [作業運行頻率] ,

        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 [循環間隔] ,

        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 [計劃運行頻率] ,

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

              '-') AS [作業啟用開始時間] ,

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

              '-') AS [作業啟用結束時間] ,

        [date_created] AS [作業創建日期] ,

        [date_modified] AS [作業上次修改日期]

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

ORDER BY [作業計劃名稱]


  


?

SQL Server 作業監控


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 手机看片福利 | 国产91精品一区二区视色 | 久久精品成人欧美大片免费 | 国外成人在线视频 | 欧美国产亚洲精品高清不卡 | 国产成人久久久精品毛片 | 亚洲免费大片 | 请看一下欧美一级毛片 | 狠狠色噜噜狠狠狠米奇9999 | 五月天国产 | 5g影院天天5g天天爽精品 | 成视频年人黄网站免费视频 | 精品一区二区三区视频在线观看免 | 在线亚洲成人 | 9999热| 久久a热6| 色综合综合 | 伊人影院综合 | 国产精品一区在线播放 | 亚洲国产一区二区三区 | 久操伊人 | 夜夜久久 | 亚洲综合在线另类色区奇米 | 亚洲精品99久久一区二区三区 | 国产免费人人看大香伊 | 欧美精品成人一区二区视频一 | 激情五月婷婷基地 | 国产一区中文字幕 | julia在线播放| 久久久亚洲国产精品主播 | 欧美成人aa大片拍拍拍 | 久久精品福利视频 | 日韩1| 97久久久久国产精品嫩草影院 | 日本不卡免费高清一级视频 | 看全色黄大色大片免费视频 | 国产精品视频免费播放 | 国产午夜亚洲精品第一区 | 国产精品一区久久精品 | 在线视频综合视频免费观看 | 青青99|