统计MSSQL中的Job情况

SELECT J.name 作业名称, 2 P.step_id‘步骤编号‘,P.step_name‘步骤名称‘,p.SubSystem ‘步骤类型‘,P.command‘执行命令‘, 3 CASE freq_type 4 WHEN 1 THEN 5 ‘只执行一次‘ 6 WHEN 4 THEN 7 ‘每日‘ 8 WHEN 8 THEN 9 ‘每周‘ 10 WHEN 16 THEN 11 ‘每月‘ 12 WHEN 32 THEN 13 ‘每月‘ 14 WHEN 64 THEN 15 ‘当 SQLServerAgent 服务启动时运行‘ 16 WHEN 128 THEN 17 ‘计算机空闲时运行‘ 18 ELSE ‘‘ 19 END 频率, 20 CASE freq_type 21 WHEN 8 then 22 CASE 23 WHEN freq_interval&1 = 1 THEN 24 ‘星期日‘ 25 ELSE ‘‘ 26 END + 27 CASE 28 WHEN freq_interval&2 = 2 THEN 29 ‘星期一‘ 30 ELSE ‘‘ 31 END + 32 CASE 33 WHEN freq_interval&4 = 4 THEN 34 ‘星期二‘ 35 ELSE ‘‘ 36 END + 37 CASE 38 WHEN freq_interval&8 = 8 THEN 39 ‘星期三‘ 40 ELSE ‘‘ 41 END + 42 CASE 43 WHEN freq_interval&16=16 THEN 44 ‘星期四‘ 45 ELSE ‘‘ 46 END + 47 CASE 48 WHEN freq_interval&32=32 THEN 49 ‘星期五‘ 50 ELSE ‘‘ 51 END + 52 CASE 53 WHEN freq_interval&64=64 THEN 54 ‘星期六‘ 55 ELSE ‘‘ 56 END 57 WHEN 16 THEN 58 ‘第‘+ltrim(str(freq_interval))+‘天‘ 59 ELSE ‘‘ 60 END 指定 , stuff(stuff(right(‘000000‘+ltrim(str(active_start_time)),6),3,0,‘:‘),6,0,‘:‘) 启动时间, 61 CASE freq_subday_type 62 WHEN 4 THEN 63 ‘每‘+ltrim(str(freq_subday_interval))+‘分钟‘ 64 WHEN 8 THEN 65 ‘每‘+ltrim(str(freq_subday_interval))+‘小时‘ 66 ELSE ‘‘ 67 END ‘间隔 ‘, 68 CASE J.enabled 69 WHEN 0 THEN 70 ‘禁用‘ 71 WHEN 1 THEN 72 ‘启用‘ 73 ELSE ‘‘ 74 END ‘状态‘, 75 CASE P.last_run_outcome 76 WHEN 0 THEN 77 ‘失败‘ 78 WHEN 1 THEN 79 ‘成功‘ 80 ELSE ‘‘ 81 END ‘上次执行‘ , P.last_run_duration‘执行时间(秒)‘, str(last_run_date)+‘ ‘+stuff(stuff(right(‘000000‘+ltrim(str(last_run_time)),6),3,0,‘:‘),6,0,‘:‘) ‘上次启动时间‘ 82 FROM msdb.dbo.sysschedules S 83 INNER JOIN msdb.dbo.sysjobschedules SCH 84 ON SCH.schedule_id=S.schedule_id 85 INNER JOIN msdb.dbo.sysjobs J 86 ON SCH.job_id = J.job_id 87 INNER JOIN msdb.dbo.sysjobsteps P 88 ON SCH.job_id = P.job_id 89 ORDER BY 启动时间

 

统计MSSQL中的Job情况

标签：禁用   cti   统计   计算   interval   时间   job   gen   间隔