怎么查看JOB的执行情况

发布网友 发布时间：2022-04-10 11:30

我来回答

共2个回答

懂视网 时间：2022-04-10 15:52

testtemp as( SELECT sch.job_id,--his.[server] as InstanceName CONVERT(nvarchar(150),his.[server]) as InstanceName ,--job.NAME as job_name CONVERT(nvarchar(150),job.NAME) as job_name ,job.[enabled] as job_enabled , --schs.[name] AS [ScheduleName] CONVERT(nvarchar(150),schs.[name]) as ScheduleName , CASE schs.[enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' else 'unknow' END AS [IsEnabled] , 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' else 'unkown' END [ScheduleType] , 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' else 'unkown' END [Occurrence] , 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)' else 'unknown' END AS [Recurrence] , 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, ':') else 'unkown' END [Frequency] ,job.category_id ,job.[description] as job_description ,his.sql_message_id ,his.sql_severity ,his.[message] as job_message ,his.run_status as last_run_status ,job.date_created ,'LastRunDateTime' = CASE WHEN his.run_date = 0 THEN null ELSE msdb.dbo.agent_datetime(his.run_date, his.run_time) END ,'NextRunDateTime' = CASE WHEN sch.next_run_date = 0 THEN null ELSE msdb.dbo.agent_datetime(sch.next_run_date, sch.next_run_time) END ,dateadd(second,(run_duration / 10000 * 3600 + (run_duration / 100) % 100 * 60 + run_duration % 100),msdb.dbo.agent_datetime(his.run_date, his.run_time)) as LastRunFinishDateTime ,((run_duration / 10000 * 3600 + (run_duration / 100) % 100 * 60 + run_duration % 100)) AS 'RunDurationSeconds' FROM msdb..sysjobschedules AS sch INNER JOIN msdb..sysjobs AS job ON sch.job_id = job.job_id inner join msdb..sysschedules as schs on sch.schedule_id=schs.schedule_id LEFT JOIN (select hi.* from msdb..sysjobhistory as hi inner join (select job_id,max(instance_id) as instance_id from msdb..sysjobhistory jh where jh.step_id = 0 GROUP BY jh.job_id) as jh on hi.job_id = jh.job_id and hi.instance_id = jh.instance_id) AS his ON his.job_id = job.job_id ) select isnull(job_id, null) as job_id, isnull(InstanceName,'')as InstanceName, isnull(job_name,'')as job_name, isnull(job_enabled,0) as job_enabled, isnull(ScheduleName,'')as ScheduleName, isnull(IsEnabled,'')as IsEnabled, isnull(ScheduleType, '')as ScheduleType, isnull(Occurrence, '')as Occurrence, isnull(Recurrence,'')as Recurrence, isnull(Frequency,'')as Frequency, isnull(category_id, 0)as category_id, isnull(job_description , '')as job_description, isnull(sql_message_id, 0)as sql_message_id, isnull(sql_severity,0)as sql_severity, isnull(job_message , '')as job_message, isnull(last_run_status,0)as last_run_status, isnull(date_created , '1900-01-01')as date_created, isnull(LastRunDateTime,'1900-01-01')as LastRunDateTime, isnull(NextRunDateTime,'1900-01-01')as NextRunDateTime, isnull(LastRunFinishDateTime,'1900-01-01')as LastRunFinishDateTime, isnull(RunDurationSeconds,0)as RunDurationSeconds from testtemp where job_name<>'syspolicy_purge_history'

获取结果：

sqlserver获取代理服务作业job的执行情况

标签：let   执行   post   分享   desc   ken   code   结果   seve   

热心网友 时间：2022-04-10 13:00

select J.name 作业名称,P.step_id'步骤编号',P.step_name'步骤名称',P.command'执行命令',
case freq_type
when 1 then '只执行一次'
when 4 then '每日'
when 8 then '每周'
when 16 then '每月'
when 32 then '每月'
when 64 then '当 SQLServerAgent 服务启动时运行'
when 128 then '计算机空闲时运行'
else '' end 频率,
case freq_type
when 8 then case when freq_interval&1 = 1 then '星期日' else '' end +
case when freq_interval&2 = 2 then '星期一' else '' end +
case when freq_interval&4 = 4 then '星期二' else '' end +
case when freq_interval&8 = 8 then '星期三' else '' end +
case when freq_interval&16=16 then '星期四' else '' end +
case when freq_interval&32=32 then '星期五' else '' end +
case when freq_interval&64=64 then '星期六' else '' end
when 16 then '第'+ltrim(str(freq_interval))+'天'
else '' end 指定 ,
stuff(stuff(right('000000'+ltrim(str(active_start_time)),6),3,0,':'),6,0,':') 启动时间,
case freq_subday_type
when 4 then '每'+ltrim(str(freq_subday_interval))+'分钟'
when 8 then '每'+ltrim(str(freq_subday_interval))+'小时'
else '' end '间隔 ',
case J.enabled
when 0 then '禁用'
when 1 then '启用'
else '' end '状态',
case P.last_run_outcome
when 0 then '失败'
when 1 then '成功'
else '' end '上次执行' ,
P.last_run_ration'执行时间(秒)',
str(last_run_date)+' '+stuff(stuff(right('000000'+ltrim(str(last_run_time)),6),3,0,':'),6,0,':') '上次启动时间'
from msdb.dbo.sysjobscheles S
inner join msdb.dbo.sysjobs J on S.job_id = J.job_id
inner join msdb.dbo.sysjobsteps P on S.job_id = P.job_id
order by 启动时间