获取指定时间区间作业运行情况

背景：数据库服务器定期重启，想知道重启期间对作业的影响。通俗点就是服务器在重启的这段时间，有哪些作业计划运行，重启后是否需要手动执行这些作业？
第一次重启的时候，按照最笨的方式，把所有作业看一遍，然后人为判断有哪些作业将受到影响，再根据作业具体代码，确定是否需手动执行。后来老大说要弄个过程出来，通过传入起止时间参数，返回区间内的作业计划。
PS:参考各类资料，修改过很多遍，最后成型在6月初，很多细节上的修改自己也记不清楚了，一直懒得整理。先放上代码，以及效果图。

  1 /************************************************************
  2  * 说明：输入将来的起、止时间，得到时间区间内作业计划情况。主要包括：
  3          区间内首次执行时间、区间内末次执行时间、区间内执行次数、循环间隔、每天执行频率等。
  4  * 使用：DBA_Pro_GetJobSchedules '20140612 07:00:00.000','20140612 08:00:00.000'
  5  * 补充：手动运行作业，不会影响作业的计划调度。
  6  ************************************************************/
  7 CREATE PROC DBA_Pro_GetJobSchedules
  8 @Starttime DATETIME,
  9 @Endtime DATETIME
 10 AS
 11 
 12 DECLARE @Midtime DATETIME
 13 IF (@Starttime < GETDATE() OR @Starttime >= @Endtime)
 14 BEGIN
 15     PRINT('请输入将来的起、止时间，且开始时间小于结束时间！')
 16     RETURN
 17 END
 18 
 19 CREATE TABLE #RunningJobs
 20 (
 21     name                        VARCHAR(128)--作业名称
 22    ,schedule_id                 INT--调度id
 23    ,next_scheduled_run_date     DATETIME--下次运行时间 yyyy-mm-dd hh:mi:ss:mmm
 24    ,active_start_time           DATETIME--执行间隔:开始时间 yyyy-mm-dd hh:mi:ss:mmm
 25    ,active_end_time             DATETIME--执行间隔:结束时间 yyyy-mm-dd hh:mi:ss:mmm
 26    ,starttime                   DATETIME--运行区间开始时间,比较传入的@Starttime和active_start_time，取大者
 27    ,endtime                     DATETIME--运行区间结束时间,比较传入的@Endtime和active_end_time，取小者
 28    ,daystatus                   INT--日期状态,用于标记当天是否满足作业运行日期，为0时当天将运行
 29    ,timestaus                   INT--时间状态,用于标记当天只执行一次的作业是否已运行，为0时当天将运行
 30 )
 31 
 32 WHILE (@Starttime < @Endtime)
 33 BEGIN
 34     SELECT @Midtime = CASE 
 35                            WHEN DATEADD(DAY ,1 ,@Starttime) > @Endtime THEN @Endtime
 36                            ELSE CONVERT(VARCHAR ,DATEADD(DAY ,1 ,@starttime) ,112)
 37                       END
 38     
 39     INSERT INTO #RunningJobs
 40     SELECT sj.name
 41           ,scd.schedule_id
 42           ,c.next_scheduled_run_date
 43           ,CONVERT(
 44                DATETIME
 45               ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' +
 46                STUFF(
 47                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)) ,6),3,0,':')
 48                   ,6,0,':'
 49                )
 50            ) [active_start_time]
 51           ,CONVERT(
 52                DATETIME
 53               ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' +
 54                STUFF(
 55                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)) ,6),3,0,':')
 56                   ,6,0,':'
 57                )
 58            ) [active_end_time]
 59           ,CASE 
 60                 WHEN @Starttime <
 61                      CONVERT(
 62                          DATETIME
 63                         ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' +
 64                          STUFF(
 65                              STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)) ,6),3,0,':')
 66                             ,6,0,':'
 67                          )
 68                      ) THEN CONVERT(
 69                          DATETIME
 70                         ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' +
 71                          STUFF(
 72                              STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)) ,6),3,0,':')
 73                             ,6,0,':'
 74                          )
 75                      )
 76                 ELSE @Starttime
 77            END     starttime
 78           ,CASE 
 79                 WHEN @Midtime >
 80                      CONVERT(
 81                          DATETIME
 82                         ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' +
 83                          STUFF(
 84                              STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)) ,6),3,0,':')
 85                             ,6,0,':'
 86                          )
 87                      ) THEN CONVERT(
 88                          DATETIME
 89                         ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' +
 90                          STUFF(
 91                              STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)) ,6),3,0,':')
 92                             ,6,0,':'
 93                          )
 94                      )
 95                 ELSE @Midtime
 96            END     endtime
 97           ,CASE freq_type
 98                 WHEN 1 --OneTime
 99                       THEN DATEDIFF(day ,next_scheduled_run_date ,@Starttime)
100                 WHEN 4 --Daily
101                       THEN DATEDIFF(dd ,next_scheduled_run_date ,@Starttime)%freq_interval
102                 WHEN 8 --Weekly
103                       THEN DATEDIFF(week ,next_scheduled_run_date ,@Starttime)%freq_recurrence_factor
104                      +
105                      CASE 
106                           WHEN freq_interval & 1 = POWER(2 ,DATEPART(dw ,@Starttime) -1)
107                                OR freq_interval & 2 = POWER(2 ,DATEPART(dw ,@Starttime) -1)
108                                OR freq_interval & 4 = POWER(2 ,DATEPART(dw ,@Starttime) -1)
109                                OR freq_interval & 8 = POWER(2 ,DATEPART(dw ,@Starttime) -1)
110                                OR freq_interval & 16 = POWER(2 ,DATEPART(dw ,@Starttime) -1)
111                                OR freq_interval & 32 = POWER(2 ,DATEPART(dw ,@Starttime) -1)
112                                OR freq_interval & 64 = POWER(2 ,DATEPART(dw ,@Starttime) -1) THEN 0
113                      END
114                 WHEN 16 --Monthly
115                       THEN DATEDIFF(MONTH ,next_scheduled_run_date ,@Starttime)%freq_recurrence_factor
116                      +
117                      CASE 
118                           WHEN freq_interval = DAY(@Starttime) THEN 0
119                      END
120                 WHEN 32 --Monthly - Relative to Frequency Interval
121                       THEN DATEDIFF(MONTH ,next_scheduled_run_date ,@Starttime)%freq_recurrence_factor
122                      +
123                      CASE 
124                           WHEN freq_interval = DATEPART(dw ,@Starttime) --month of week1-7
125                                AND 
126                                (
127                                    (
128                                        freq_relative_interval <= 8
129                                        AND freq_relative_interval = POWER(
130                                                2
131                                               ,DATEDIFF(DAY ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' ,@Starttime) / 7
132                                            )
133                                    )
134                                    OR (
135                                           freq_relative_interval = 16
136                                           AND DATEDIFF(
137                                                   DAY
138                                                  ,@Starttime
139                                                  ,DATEADD(
140                                                       DAY
141                                                      ,-1
142                                                      ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
143                                                   )
144                                               ) < 7
145                                       )
146                                ) THEN 0
147                           WHEN freq_interval = 8 --month of day
148                                AND

149                                (
150                                    (
151                                        freq_relative_interval = 1
152                                        AND DATEDIFF(DAY ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' ,@Starttime) = 0
153                                    )
154                                    OR (
155                                           freq_relative_interval = 2
156                                           AND DATEDIFF(DAY ,CONVERT(CHAR(6) ,@Starttime ,112) + '02' ,@Starttime) = 0
157                                       )
158                                    OR (
159                                           freq_relative_interval = 4
160                                           AND DATEDIFF(DAY ,CONVERT(CHAR(6) ,@Starttime ,112) + '03' ,@Starttime) = 0
161                                       )
162                                    OR (
163                                           freq_relative_interval = 8
164                                           AND DATEDIFF(DAY ,CONVERT(CHAR(6) ,@Starttime ,112) + '04' ,@Starttime) = 0
165                                       )
166                                    OR (
167                                           freq_relative_interval = 16
168                                           AND CONVERT(VARCHAR ,@Starttime ,112) = 
169                                               CONVERT(
170                                                   VARCHAR
171                                                  ,DATEADD(
172                                                       DAY
173                                                      ,-1
174                                                      ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
175                                                   )
176                                                  ,112
177                                               )
178                                       )
179                                ) THEN 0
180                           WHEN freq_interval = 9 --month of weekday
181                                AND
182                                (
183                                    (
184                                        freq_relative_interval = 1
185                                        AND DATEADD(
186                                                DAY
187                                               ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
188                                                     WHEN 1 THEN 1
189                                                     WHEN 7 THEN 2
190                                                     ELSE 0
191                                                END
192                                               ,CONVERT(CHAR(6) ,@Starttime ,112) + '01'
193                                            ) = CONVERT(VARCHAR ,@Starttime ,112)
194                                    )
195                                    OR (
196                                           freq_relative_interval = 2
197                                           AND DATEADD(
198                                                   DAY
199                                                  ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
200                                                        WHEN 6 THEN 3
201                                                        WHEN 7 THEN 3
202                                                        WHEN 1 THEN 2
203                                                        ELSE 1
204                                                   END
205                                                  ,CONVERT(CHAR(6) ,@Starttime ,112) + '01'
206                                               ) = CONVERT(VARCHAR ,@Starttime ,112)
207                                       )
208                                    OR (
209                                           freq_relative_interval = 4
210                                           AND DATEADD(
211                                                   DAY
212                                                  ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
213                                                        WHEN 5 THEN 4
214                                                        WHEN 6 THEN 4
215                                                        WHEN 7 THEN 4
216                                                        WHEN 1 THEN 3
217                                                        ELSE 2
218                                                   END
219                                                  ,CONVERT(CHAR(6) ,@Starttime ,112) + '01'
220                                               ) = CONVERT(VARCHAR ,@Starttime ,112)
221                                       )
222                                    OR (
223                                           freq_relative_interval = 8
224                                           AND DATEADD(
225                                                   DAY
226                                                  ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
227                                                        WHEN 4 THEN 5
228                                                        WHEN 5 THEN 5
229                                                        WHEN 6 THEN 5
230                                                        WHEN 7 THEN 5
231                                                        WHEN 1 THEN 4
232                                                        ELSE 3
233                                                   END
234                                                  ,CONVERT(CHAR(6) ,@Starttime ,112) + '01'
235                                               ) = CONVERT(VARCHAR ,@Starttime ,112)
236                                       )
237                                    OR (
238                                           freq_relative_interval = 16
239                                           AND DATEADD(
240                                                   DAY
241                                                  ,CASE DATEPART(
242                                                            dw
243                                                           ,DATEADD(
244                                                                DAY
245                                                               ,-1
246                                                               ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
247                                                            )
248                                                        )
249                                                        WHEN 1 THEN -2
250                                                        WHEN 7 THEN -1
251                                                        ELSE 0
252                                                   END
253                                                  ,DATEADD(
254                                                       DAY
255                                                      ,-1
256                                                      ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
257                                                   )
258                                               ) = CONVERT(VARCHAR ,@Starttime ,112)
259                                       )
260                                ) THEN 0
261                           WHEN freq_interval = 10 --month of weekend
262                                AND
263                                (
264                                    (
265                                        freq_relative_interval = 1
266                                        AND DATEADD(
267                                                DAY
268                                               ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
269                                                     WHEN 2 THEN 5
270                                                     WHEN 3 THEN 4
271                                                     WHEN 4 THEN 3
272                                                     WHEN 5 THEN 2
273                                                     WHEN 6 THEN 1
274                                                     ELSE 0
275                                                END
276                                               ,CONVERT(CHAR(6) ,@Starttime ,112) + '01'
277                                            ) = CONVERT(VARCHAR ,@Starttime ,112)
278                                    )
279                                    OR (
280                                           freq_relative_interval = 2
281                                           AND DATEADD(
282                                                   DAY
283                                                  ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
284                                                        WHEN 3 THEN 5
285                                                        WHEN 4 THEN 4
286                                                        WHEN 5 THEN 3
287                                                        WHEN 6 THEN 2
288                                             WHEN 7 THEN 1
289                                                        ELSE 6
290                                                   END
291                                                  ,CONVERT(CHAR(6) ,@Starttime ,112) + '01'
292                                               ) = CONVERT(VARCHAR ,@Starttime ,112)
293                                       )
294                                    OR (
295                                           freq_relative_interval = 4
296                                           AND DATEADD(
297                                                   DAY
298                                                  ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
299                                                        WHEN 2 THEN 12
300                                                        WHEN 3 THEN 11
301                                                        WHEN 4 THEN 10
302                                                        WHEN 5 THEN 9
303                                                        WHEN 6 THEN 8
304                                                        ELSE 7
305                                                   END
306                                                  ,CONVERT(CHAR(6) ,@Starttime ,112) + '01'
307                                               ) = CONVERT(VARCHAR ,@Starttime ,112)
308                                       )
309                                    OR (
310                                           freq_relative_interval = 8
311                                           AND DATEADD(
312                                                   DAY
313                                                  ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
314                                                        WHEN 3 THEN 12
315                                                        WHEN 4 THEN 11
316                                                        WHEN 5 THEN 10
317                                                        WHEN 6 THEN 9
318                                                        WHEN 7 THEN 8
319                                                        ELSE 13
320                                                   END
321                                                  ,CONVERT(CHAR(6) ,@Starttime ,112) + '01'
322                                               ) = CONVERT(VARCHAR ,@Starttime ,112)
323                                       )
324                                    OR (
325                                           freq_relative_interval = 16
326                                           AND DATEADD(
327                                                   DAY
328                                                  ,CASE DATEPART(
329                                                            dw
330                                                           ,DATEADD(
331                                                                DAY
332                                                               ,-1
333                                                               ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
334                                                            )
335                                                        )
336                                                        WHEN 2 THEN -1
337                                                        WHEN 3 THEN -2
338                                                        WHEN 4 THEN -3
339                                                        WHEN 5 THEN -4
340                                                        WHEN 6 THEN -5
341                                                        ELSE 0
342                                                   END
343                                                  ,DATEADD(
344                                                       DAY
345                                                      ,-1
346                                                      ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01')
347                                                   )
348                                               ) = CONVERT(VARCHAR ,@Starttime ,112)
349                                       )
350                                ) THEN 0
351                      END
352            END  AS daystatus
353           ,CASE 
354                 WHEN freq_subday_type IN (0 ,1) 
355                      THEN 
356                      CASE 
357                           WHEN active_start_time*1000
358                                >= CONVERT(INT ,REPLACE(CONVERT(VARCHAR ,@Starttime ,114) ,':' ,'')) 
359                                THEN 0
360                           ELSE 1
361                      END
362                 ELSE 0
363            END AS timestaus
364     FROM   [msdb].[dbo].[sysschedules] scd
365            INNER JOIN msdb.dbo.sysjobschedules sjsc
366                 ON  scd.schedule_id = sjsc.schedule_id
367            INNER JOIN (
368                     SELECT a.job_id
369                           ,a.next_scheduled_run_date
370                     FROM   msdb.dbo.sysjobactivity a
371                            INNER JOIN (
372                                     SELECT MAX(session_id) session_id
373                                     FROM   msdb.dbo.syssessions
374                                 ) b
375                                 ON  a.session_id = b.session_id
376                 ) c
377                 ON  sjsc.job_id = c.job_id
378            INNER JOIN msdb.dbo.sysjobs sj

379                 ON  c.job_id = sj.job_id
380     WHERE  scd.enabled = 1
381            AND sj.enabled = 1
382            --AND c.next_scheduled_run_date <= @Midtime
383     
384     SET @Starttime = CONVERT(VARCHAR ,DATEADD(DAY ,1 ,@starttime) ,112)
385 END
386 --select * from #RunningJobs order by name,active_start_time
387 SELECT name--作业名称
388       ,plan_type--计划类型
389       ,frequency--执行频率每天、每周、每月
390       ,next_scheduled_run_date--下次计划运行时间
391       ,FirstRunTimeInRange--区间内首次运行时间
392       ,LastRunTimeInRange--区间内末次运行时间
393       ,RunTimesInRange--区间内运行次数
394       ,intercycle--循环间隔
395       ,execution_interval--每天执行频率及区间
396       ,active_start_date--开始日期
397       ,active_end_date--结束日期
398       ,date_created--创建时间
399       ,date_modified--修改时间
400 FROM   (
401            SELECT a.name
402                  ,a.schedule_id
403                  ,a.active_start_time
404                  ,a.starttime
405                  ,a.endtime
406                  ,freq_type,freq_subday_type
407                  ,freq_subday_interval
408                  ,CASE 
409                        WHEN [freq_type] = 64 THEN 
410                             'Start automatically when SQL Server Agent starts'
411                        WHEN [freq_type] = 128 THEN 
412                             'Start whenever the CPUs become idle'
413                        WHEN [freq_type] IN (4 ,8 ,16 ,32) THEN 'Recurring'
414                        WHEN [freq_type] = 1 THEN 'One Time'
415                   END                    AS plan_type
416                  ,CASE [freq_type]
417                        WHEN 1 THEN 'One Time'
418                        WHEN 4 THEN 'Daily'
419                        WHEN 8 THEN 'Weekly'
420                        WHEN 16 THEN 'Monthly'
421                        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
422                        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
423                        WHEN 128 THEN 'Start whenever the CPUs become idle'
424                   END                    AS frequency
425                  ,a.next_scheduled_run_date
426                  ,CASE [freq_subday_type]
427                        WHEN 2 THEN DATEADD(
428                                 second
429                                ,(
430                                     freq_subday_interval -
431                                     DATEDIFF(second ,a.active_start_time ,a.starttime)
432                                     %freq_subday_interval
433                                 )%freq_subday_interval
434                                ,a.starttime
435                             )
436                        WHEN 4 THEN DATEADD(
437                                 second
438                                ,(
439                               freq_subday_interval * 60 -
440                                     DATEDIFF(second ,a.active_start_time ,a.starttime)
441                                     %(freq_subday_interval * 60)
442                                 )%(freq_subday_interval * 60)
443                                ,a.starttime
444                             )
445                        WHEN 8 THEN DATEADD(
446                                 second
447                                ,(
448                                     freq_subday_interval * 3600 -
449                                     DATEDIFF(second ,a.active_start_time ,a.starttime)
450                                     %(freq_subday_interval * 3600)
451                                 )%(freq_subday_interval * 3600)
452                                ,a.starttime
453                             )
454                        ELSE a.active_start_time
455                   END                    AS FirstRunTimeInRange
456                  ,CASE [freq_subday_type]
457                        WHEN 2 THEN DATEADD(
458                                 second
459                                ,-DATEDIFF(second ,a.active_start_time ,a.endtime)
460                                 %freq_subday_interval
461                                ,a.endtime
462                             )
463                        WHEN 4 THEN DATEADD(
464                                 second
465                                ,-DATEDIFF(second ,a.active_start_time ,a.endtime)
466                                 %(freq_subday_interval * 60)
467                                ,a.endtime
468                             )
469                        WHEN 8 THEN DATEADD(
470                                 second
471                                ,-DATEDIFF(second ,a.active_start_time ,a.endtime)
472                                 %(freq_subday_interval * 3600)
473                                ,a.endtime
474                             )
475                        ELSE a.active_start_time
476                   END                    AS LastRunTimeInRange
477                  ,CASE [freq_subday_type]
478                        WHEN 2 THEN DATEDIFF(
479                                 second
480                                ,DATEADD(
481                                     second
482                                    ,(
483                                         freq_subday_interval -
484                                         DATEDIFF(second ,a.active_start_time ,a.starttime)
485                                         %freq_subday_interval
486                                     )%freq_subday_interval
487                                    ,a.starttime
488                                 )
489                                ,a.endtime
490                             ) / (freq_subday_interval) + 1
491                        WHEN 4 THEN DATEDIFF(
492                                 second
493                                ,DATEADD(
494                                     second
495                                    ,(
496                                         freq_subday_interval * 60 -
497                                         DATEDIFF(second ,a.active_start_time ,a.starttime)
498                                         %(freq_subday_interval * 60)
499                                     )%(freq_subday_interval * 60)
500                                    ,a.starttime
501                                 )
502                                ,a.endtime
503                             ) / (freq_subday_interval * 60) + 1
504                        WHEN 8 THEN DATEDIFF(
505                                 second
506                                ,DATEADD(
507                                     second
508                                    ,(
509                                         freq_subday_interval * 3600 -
510                                         DATEDIFF(second ,a.active_start_time ,a.starttime)
511                                         %(freq_subday_interval * 3600)
512                                     )%(freq_subday_interval * 3600)
513                                    ,a.starttime
514                               )
515                                ,a.endtime
516                             ) / (freq_subday_interval * 3600) + 1
517                        ELSE 1
518                   END                    AS RunTimesInRange
519                  ,CASE [freq_type]
520                        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3))
521                             + ' day(s)'
522                        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
523                             + ' week(s) on '
524                             + CASE 
525                                    WHEN [freq_interval] & 1 = 1 THEN 'Sunday'
526                                    ELSE ''
527                               END 
528                             + CASE 
529                                    WHEN [freq_interval] & 2 = 2 THEN ', Monday'
530                                    ELSE ''
531                               END
532                             + CASE 
533                                    WHEN [freq_interval] & 4 = 4 THEN ', Tuesday'
534                                    ELSE ''
535                               END 
536                             + CASE 
537                                    WHEN [freq_interval] & 8 = 8 THEN ', Wednesday'
538                                    ELSE ''
539                               END
540                             + CASE 
541                                    WHEN [freq_interval] & 16 = 16 THEN ', Thursday'
542                                    ELSE ''
543                               END 
544                             + CASE 
545                                    WHEN [freq_interval] & 32 = 32 THEN ', Friday'
546                                    ELSE ''
547                               END
548                             + CASE 
549                                    WHEN [freq_interval] & 64 = 64 THEN ', Saturday'
550                                    ELSE ''
551                               END
552                        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))
553                             + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
554                             + ' month(s)'
555                        WHEN 32 THEN 'Occurs on ' + CASE [freq_relative_interval]
556                                                         WHEN 1 THEN 'First'
557                                                         WHEN 2 THEN 'Second'
558                                                         WHEN 4 THEN 'Third'
559                                                         WHEN 8 THEN 'Fourth'
560                                                         WHEN 16 THEN 'Last'
561                                                    END + ' ' + CASE 
562                                                                     [freq_interval]
563                                                                     WHEN 1 THEN 
564                                                                          'Sunday'
565                                                                     WHEN 2 THEN 
566                                                                          'Monday'
567                                                                     WHEN 3 THEN 
568                                                                          'Tuesday'
569                                                                     WHEN 4 THEN 
570                                                                          'Wednesday'
571                                                                     WHEN 5 THEN 
572                                                                          'Thursday'
573                                                                     WHEN 6 THEN 
574                                                                          'Friday'
575                                                                     WHEN 7 THEN 
576                                                                          'Saturday'
577                                                                     WHEN 8 THEN 
578                                                                          'Day'
579                                                                     WHEN 9 THEN 
580                                                                          'Weekday'
581                                                                     WHEN 10 THEN 
582                                                                          'Weekend day'
583                                                                END +
584                             ' of every '
585                             + CAST([freq_recurrence_factor] AS VARCHAR(3)) +
586                             ' month(s)'
587                   END                    AS intercycle
588                  ,CASE [freq_subday_type]
589                        WHEN 1 THEN 'Occurs once at ' + CONVERT(VARCHAR ,a.active_start_time ,108)
590                        WHEN 2 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3))
591                             + ' Second(s) between ' + CONVERT(VARCHAR ,a.active_start_time ,108)
592                             + ' and ' + CONVERT(VARCHAR ,a.active_end_time ,108)
593                        WHEN 4 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) 
594                             + ' Minute(s) between ' + CONVERT(VARCHAR ,a.active_start_time ,108)
595                             + ' and ' + CONVERT(VARCHAR ,a.active_end_time ,108)
596                        WHEN 8 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3))
597                             + ' Hour(s) between ' + CONVERT(VARCHAR ,a.active_start_time ,108)
598                             + ' and ' + CONVERT(VARCHAR ,a.active_end_time ,108)
599                   END                    AS execution_interval
600                  ,STUFF(
601                       STUFF(CAST([active_start_date] AS VARCHAR(8)) ,5 ,0 ,'-')
602                      ,8,0,'-'
603                   )                      AS active_start_date
604                  ,STUFF(
605                       STUFF(CAST([active_end_date] AS VARCHAR(8)) ,5 ,0 ,'-')
606                      ,8,0,'-'
607                   )                      AS active_end_date
608                  ,scd.[date_created] AS date_created
609                  ,scd.[date_modified] AS date_modified
610            FROM   #RunningJobs a
611                   INNER JOIN [msdb].[dbo].[sysschedules] scd
612                        ON  a.schedule_id = scd.schedule_id
613            WHERE  a.daystatus = 0
614            AND a.timestaus=0
615        ) a
616 WHERE  a.FirstRunTimeInRange <= a.endtime
617 AND a.FirstRunTimeInRange <= CONVERT(DATETIME ,a.active_end_date + ' 23:59:59:997')
618 ORDER BY
619        a.name
620       ,a.FirstRunTimeInRange
621 
622 DROP TABLE #RunningJobs
View Code

执行过程将返回作业区间内首次执行时间、区间内末次执行时间、区间内执行次数、循环间隔、每天执行频率等信息

--15:31 2015/1/14 如果我们想把一个操作加到某个00:00:00执行作业的最后一步，我们先要把零点的那些作业找出来，此时可以利用msdb.dbo.sysjobschedules查询作业下次执行时间。注意msdb.dbo.sysjobschedules由后台线程每20分钟更新一次。

1 --下次执行时间(后台线程每20分钟更新)
2 select top 5 sj.name,sjs.next_run_date,sjs.next_run_time from msdb.dbo.sysjobs sj
3 inner join msdb.dbo.sysjobschedules sjs
4 on sj.job_id=sjs.job_id
5 where sj.enabled=1
6 and next_run_time=0--根据需求调整
7 order by sjs.next_run_date,sjs.next_run_time

对于执行频率很高的作业，msdb.dbo.sysjobschedules中的下次运行时间会滞后，如果需要实时的信息可以从msdb.dbo.sysjobactivity中获取。SQL Server代理服务每次重启后，将创建一个新的会话，并且 msdb 数据库的 sysjobactivity 表由所有现有的已定义作业填充。

1 --下次执行时间(实时更新)
2 select top 5 sj.name,sja.next_scheduled_run_date from msdb.dbo.sysjobs sj
3 inner join 
4 (select * from msdb.dbo.sysjobactivity
5 where session_id in(select max(session_id) session_id from msdb.dbo.sysjobactivity) 
6 )sja
7 on sj.job_id=sja.job_id
8 where sj.enabled=1
9 order by sja.next_scheduled_run_date

实时更新的代码对应“SQL Server 代理->作业活动监视器”的内容，里面可以设置筛选、排序。

posted @ 2014-07-22 21:32  Uest  阅读(968)  评论(0)  编辑  收藏  举报