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

以下脚本为获取sqlserver的执行job

with 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'

获取结果：

转载于:https://www.cnblogs.com/airven/p/8383985.html