Select sched.*,jobsched.* FROM msdb.dbo.sysschedules AS sched inner Join msdb.dbo.sysjobschedules AS jobsched ON sched.schedule_id = jobsched.schedule_id
1
1
ジョブの次の実行日を手動で計算する必要がありますが、手伝ってもらえますか？
4 回答
6
ジョブの次の実行日を取得するには、http：//msdn.microsoft.com/en-us/library/ms178644.aspx [sysschedules]およびhttp://msdn.microsoft.com/en-us/を使用できます。 library / ms188924.aspx [sysjobschedules]テーブル
テーブルhttp://msdn.microsoft.com/en-us/library/ms188924.aspx[sysjobschedules]のnext_run_dateおよびnext_runtime列を確認します。
_ _ next_run_date intジョブの実行がスケジュールされている次の日付。 日付はYYYYMMDD形式です。
next_run_time intジョブの実行がスケジュールされている時刻。 時刻はHHMMSSでフォーマットされ、24時間クロックを使用します。 _ _
このスクリプトを参照してください
Select sched.*,jobsched.* FROM msdb.dbo.sysschedules AS sched inner Join msdb.dbo.sysjobschedules AS jobsched ON sched.schedule_id = jobsched.schedule_id
または、http：//msdn.microsoft.com/en-us/library/ms176046.aspx [msdb.dbo.sp_help_jobschedule]ストアドプロシージャを使用して、同じ情報を取得できます。
更新
next_run_dateを手動で計算する必要がある場合は、http：//msdn.microsoft.com/en-us/library/ms178644.aspx [* sysschedules *]テーブルを確認し、freq_interval、freq_subday_type、freq_subday_interval、freq_relative_interval、freq_recurrence_factor、active_start_dateを確認する必要があります。 、active_start_time列で式を決定します。
このhttp://www.mssqltips.com/tip.asp?tip=1622[link]で使用例を確認してください。
2
これを確認してください。
SELECT
J.NAME JOB,
DATEADD(SS,(H.RUN_TIME)%100,DATEADD(N,(H.RUN_TIME/100)%100,DATEADD(HH,H.RUN_TIME/10000,CONVERT(DATETIME,CONVERT(VARCHAR(8),H.RUN_DATE),112))))
JOB_STARTED,
DATEADD(SS,((H.RUN_DURATION)%10000)%100,DATEADD(N,((H.RUN_DURATION)%10000)/100,DATEADD(HH,(H.RUN_DURATION)/10000,DATEADD(SS,(H.RUN_TIME)%100,DATEADD(N,(H.RUN_TIME/100)%100,DATEADD(HH,H.RUN_TIME/10000,CONVERT(DATETIME,CONVERT(VARCHAR(8),H.RUN_DATE),112)))))))
JOB_COMPLETED,
CONVERT(VARCHAR(2),(H.RUN_DURATION)/10000) + ':' +
CONVERT(VARCHAR(2),((H.RUN_DURATION)%10000)/100)+ ':' +
CONVERT(VARCHAR(2),((H.RUN_DURATION)%10000)%100) RUN_DURATION,
CASE H.RUN_STATUS
WHEN 0 THEN 'FAILED'
WHEN 1 THEN 'SUCCEEDED'
WHEN 2 THEN 'RETRY'
WHEN 3 THEN 'CANCELED'
WHEN 4 THEN 'IN PROGRESS'
END RUN_STATUS
,CASE S.FREQ_TYPE
WHEN 1 THEN 'ONCE'
WHEN 4 THEN ' DAILY'
WHEN 8 THEN ' WEEKLY'
WHEN 16 THEN ' MONTHLY'
WHEN 32 THEN ' MONTHLY RELATIVE'
WHEN 64 THEN ' WHEN SQL SERVER'
ELSE 'N/A' END [FREQ]
,CASE
WHEN S.NEXT_RUN_DATE > 0 THEN DATEADD(N,(NEXT_RUN_TIME%10000)/100,DATEADD(HH,NEXT_RUN_TIME/10000,CONVERT(DATETIME,CONVERT(VARCHAR(8),NEXT_RUN_DATE),112)))
ELSE CONVERT(DATETIME,CONVERT(VARCHAR(8),'19000101'),112) END NEXT_RUN
FROM
MSDB..SYSJOBHISTORY H,
MSDB..SYSJOBS J,
MSDB..SYSJOBSCHEDULES S,
(
SELECT
MAX(INSTANCE_ID) INSTANCE_ID
,JOB_ID
FROM MSDB..SYSJOBHISTORY GROUP BY JOB_ID
) M
WHERE
H.JOB_ID = J.JOB_ID
AND
J.JOB_ID = S.JOB_ID
AND
H.JOB_ID = M.JOB_ID
AND
H.INSTANCE_ID = M.INSTANCE_ID
-- IF you want to check all job for today then uncomments below
-- AND
-- RUN_DATE = (YEAR(GETDATE())*10000) + (MONTH(GETDATE()) * 100) + DAY(GETDATE())
ORDER BY NEXT_RUN
0
0
hereは、次回の実行日を取得できる非常に優れたスクリプトです。 。
-- http://www.sqlprofessionals.com/blog/sql-scripts/2014/10/06/insight-into-sql-agent-job-schedules/
SELECT [JobName] = [jobs].[name]
,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
,[Next_Run_Date] =
CASE [jobschedule].[next_run_date]
WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
END
,[Category] = [categories].[name]
,[Owner] = SUSER_SNAME([jobs].[owner_sid])
,[Description] = [jobs].[description]
,[Occurs] =
CASE [schedule].[freq_type]
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPU(s) become idle'
ELSE ''
END
,[Occurs_detail] =
CASE [schedule].[freq_type]
WHEN 1 THEN 'O'
WHEN 4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
WHEN 8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' +
LEFT(
CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END ,
LEN(
CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END
) - 1
)
WHEN 16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
WHEN 32 THEN 'The ' +
CASE [schedule].[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 [schedule].[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 ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
ELSE ''
END
,[Frequency] =
CASE [schedule].[freq_subday_type]
WHEN 1 THEN 'Occurs once at ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 2 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 4 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 8 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
ELSE ''
END
,[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])
FROM [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK)
LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK)
ON [jobs].[job_id] = [jobschedule].[job_id]
LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK)
ON [jobschedule].[schedule_id] = [schedule].[schedule_id]
INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK)
ON [jobs].[category_id] = [categories].[category_id]
LEFT OUTER JOIN
( SELECT [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) +
(([run_duration] % 10000) / 100 * 60) +
([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
FROM [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
WHERE [step_id] = 0
GROUP BY [job_id]
) AS [jobhistory]
ON [jobhistory].[job_id] = [jobs].[job_id];