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
