指令碼查看代理運行情況

標籤：

DECLARE @WeekDays TABLE
(
freq_interval INT,
weekdays NVARCHAR(120)

)

INSERT INTO @WeekDays
SELECT 1 ,N‘星期日 ‘ UNION ALL
SELECT 2 ,N‘星期一 ‘ UNION ALL
SELECT 4 ,N‘星期二 ‘ UNION ALL
SELECT 8 ,N‘星期三 ‘ UNION ALL
SELECT 16 ,N‘星期四 ‘ UNION ALL
SELECT 32 ,N‘星期五 ‘ UNION ALL
SELECT 64 ,N‘星期六 ‘ UNION ALL
SELECT 3 ,N‘星期日,一 ‘ UNION ALL
SELECT 5 ,N‘星期日,二 ‘ UNION ALL
SELECT 9 ,N‘星期日,三 ‘ UNION ALL
SELECT 17 ,N‘星期日,四 ‘ UNION ALL
SELECT 33 ,N‘星期日,五 ‘ UNION ALL
SELECT 65 ,N‘星期日,六 ‘ UNION ALL
SELECT 6 ,N‘星期一,二 ‘ UNION ALL
SELECT 10 ,N‘星期一,三 ‘ UNION ALL
SELECT 18 ,N‘星期一,四 ‘ UNION ALL
SELECT 34 ,N‘星期一,五 ‘ UNION ALL
SELECT 66 ,N‘星期一,六 ‘ UNION ALL
SELECT 12 ,N‘星期二,三 ‘ UNION ALL
SELECT 20 ,N‘星期二,四 ‘ UNION ALL
SELECT 36 ,N‘星期二,五 ‘ UNION ALL
SELECT 68 ,N‘星期二,六 ‘ UNION ALL
SELECT 24 ,N‘星期三,四 ‘ UNION ALL
SELECT 40 ,N‘星期三,五 ‘ UNION ALL
SELECT 72 ,N‘星期三,六 ‘ UNION ALL
SELECT 48 ,N‘星期四,五 ‘ UNION ALL
SELECT 80 ,N‘星期四,六 ‘ UNION ALL
SELECT 96 ,N‘星期五,六 ‘ UNION ALL
SELECT 7 ,N‘星期日,一,二 ‘ UNION ALL
SELECT 11 ,N‘星期日,一,三 ‘ UNION ALL
SELECT 19 ,N‘星期日,一,四 ‘ UNION ALL
SELECT 35 ,N‘星期日,一,五 ‘ UNION ALL
SELECT 67 ,N‘星期日,一,六 ‘ UNION ALL
SELECT 13 ,N‘星期日,二,三 ‘ UNION ALL
SELECT 21 ,N‘星期日,二,四 ‘ UNION ALL
SELECT 37 ,N‘星期日,二,五 ‘ UNION ALL
SELECT 69 ,N‘星期日,二,六 ‘ UNION ALL
SELECT 25 ,N‘星期日,三,四 ‘ UNION ALL
SELECT 41 ,N‘星期日,三,五 ‘ UNION ALL
SELECT 73 ,N‘星期日,三,六 ‘ UNION ALL
SELECT 49 ,N‘星期日,四,五 ‘ UNION ALL
SELECT 81 ,N‘星期日,四,六 ‘ UNION ALL
SELECT 97 ,N‘星期日,五,六 ‘ UNION ALL
SELECT 14 ,N‘星期一,二,三 ‘ UNION ALL
SELECT 22 ,N‘星期一,二,四 ‘ UNION ALL
SELECT 38 ,N‘星期一,二,五 ‘ UNION ALL
SELECT 70 ,N‘星期一,二,六 ‘ UNION ALL
SELECT 26 ,N‘星期一,三,四 ‘ UNION ALL
SELECT 42 ,N‘星期一,三,五 ‘ UNION ALL
SELECT 74 ,N‘星期一,三,六 ‘ UNION ALL
SELECT 50 ,N‘星期一,四,五 ‘ UNION ALL
SELECT 82 ,N‘星期一,四,六 ‘ UNION ALL
SELECT 98 ,N‘星期一,五,六 ‘ UNION ALL
SELECT 28 ,N‘星期二,三,四 ‘ UNION ALL
SELECT 44 ,N‘星期二,三,五 ‘ UNION ALL
SELECT 76 ,N‘星期二,三,六 ‘ UNION ALL
SELECT 52 ,N‘星期二,四,五 ‘ UNION ALL
SELECT 84 ,N‘星期二,四,六 ‘ UNION ALL
SELECT 100 ,N‘星期二,五,六 ‘ UNION ALL
SELECT 56 ,N‘星期三,四,五 ‘ UNION ALL
SELECT 88 ,N‘星期三,四,六 ‘ UNION ALL
SELECT 104 ,N‘星期三,五,六 ‘ UNION ALL
SELECT 112 ,N‘星期四,五,六 ‘ UNION ALL
SELECT 15 ,N‘星期日,一,二,三 ‘ UNION ALL
SELECT 23 ,N‘星期日,一,二,四 ‘ UNION ALL
SELECT 39 ,N‘星期日,一,二,五 ‘ UNION ALL
SELECT 71 ,N‘星期日,一,二,六 ‘ UNION ALL
SELECT 27 ,N‘星期日,一,三,四 ‘ UNION ALL
SELECT 43 ,N‘星期日,一,三,五 ‘ UNION ALL
SELECT 75 ,N‘星期日,一,三,六 ‘ UNION ALL
SELECT 51 ,N‘星期日,一,四,五 ‘ UNION ALL
SELECT 83 ,N‘星期日,一,四,六 ‘ UNION ALL
SELECT 99 ,N‘星期日,一,五,六 ‘ UNION ALL
SELECT 29 ,N‘星期日,二,三,四 ‘ UNION ALL
SELECT 45 ,N‘星期日,二,三,五 ‘ UNION ALL
SELECT 77 ,N‘星期日,二,三,六 ‘ UNION ALL
SELECT 53 ,N‘星期日,二,四,五 ‘ UNION ALL
SELECT 85 ,N‘星期日,二,四,六 ‘ UNION ALL
SELECT 101 ,N‘星期日,二,五,六 ‘ UNION ALL
SELECT 57 ,N‘星期日,三,四,五 ‘ UNION ALL
SELECT 89 ,N‘星期日,三,四,六 ‘ UNION ALL
SELECT 105 ,N‘星期日,三,五,六 ‘ UNION ALL
SELECT 113 ,N‘星期日,四,五,六 ‘ UNION ALL
SELECT 30 ,N‘星期一,二,三,四 ‘ UNION ALL
SELECT 46 ,N‘星期一,二,三,五 ‘ UNION ALL
SELECT 78 ,N‘星期一,二,三,六 ‘ UNION ALL
SELECT 54 ,N‘星期一,二,四,五 ‘ UNION ALL
SELECT 86 ,N‘星期一,二,四,六 ‘ UNION ALL
SELECT 102 ,N‘星期一,二,五,六 ‘ UNION ALL
SELECT 58 ,N‘星期一,三,四,五 ‘ UNION ALL
SELECT 90 ,N‘星期一,三,四,六 ‘ UNION ALL
SELECT 106 ,N‘星期一,三,五,六 ‘ UNION ALL
SELECT 114 ,N‘星期一,四,五,六 ‘ UNION ALL
SELECT 60 ,N‘星期二,三,四,五 ‘ UNION ALL
SELECT 92 ,N‘星期二,三,四,六 ‘ UNION ALL
SELECT 108 ,N‘星期二,三,五,六 ‘ UNION ALL
SELECT 116 ,N‘星期二,四,五,六 ‘ UNION ALL
SELECT 120 ,N‘星期三,四,五,六 ‘ UNION ALL
SELECT 31 ,N‘星期日,一,二,三,四 ‘ UNION ALL
SELECT 47 ,N‘星期日,一,二,三,五 ‘ UNION ALL
SELECT 79 ,N‘星期日,一,二,三,六 ‘ UNION ALL
SELECT 55 ,N‘星期日,一,二,四,五 ‘ UNION ALL
SELECT 87 ,N‘星期日,一,二,四,六 ‘ UNION ALL
SELECT 103 ,N‘星期日,一,二,五,六 ‘ UNION ALL
SELECT 59 ,N‘星期日,一,三,四,五 ‘ UNION ALL
SELECT 91 ,N‘星期日,一,三,四,六 ‘ UNION ALL
SELECT 107 ,N‘星期日,一,三,五,六 ‘ UNION ALL
SELECT 115 ,N‘星期日,一,四,五,六 ‘ UNION ALL
SELECT 61 ,N‘星期日,二,三,四,五 ‘ UNION ALL
SELECT 93 ,N‘星期日,二,三,四,六 ‘ UNION ALL
SELECT 109 ,N‘星期日,二,三,五,六 ‘ UNION ALL
SELECT 117 ,N‘星期日,二,四,五,六 ‘ UNION ALL
SELECT 121 ,N‘星期日,三,四,五,六 ‘ UNION ALL
SELECT 62 ,N‘星期一,二,三,四,五 ‘ UNION ALL
SELECT 94 ,N‘星期一,二,三,四,六 ‘ UNION ALL
SELECT 110 ,N‘星期一,二,三,五,六 ‘ UNION ALL
SELECT 118 ,N‘星期一,二,四,五,六 ‘ UNION ALL
SELECT 122 ,N‘星期一,三,四,五,六 ‘ UNION ALL
SELECT 124 ,N‘星期二,三,四,五,六 ‘ UNION ALL
SELECT 63 ,N‘星期日,一,二,三,四,五‘ UNION ALL
SELECT 95 ,N‘星期日,一,二,三,四,六‘ UNION ALL
SELECT 111 ,N‘星期日,一,二,三,五,六‘ UNION ALL
SELECT 119 ,N‘星期日,一,二,四,五,六‘ UNION ALL
SELECT 123 ,N‘星期日,一,三,四,五,六‘ UNION ALL
SELECT 125 ,N‘星期日,二,三,四,五,六‘ UNION ALL
SELECT 126 ,N‘星期一,二,三,四,五,六‘ UNION ALL
SELECT 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 N‘每月星期‘ + CONVERT(NVARCHAR(4),freq_interval) + N‘執行‘
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_time
FROM msdb.dbo.sysschedules s
INNER JOIN msdb.dbo.sysjobschedules d ON s.schedule_id=d.schedule_id
INNER JOIN msdb.dbo.sysjobs j ON d.job_id = j.job_id
ORDER BY j.name

指令碼查看代理運行情況