Sql-Server
如何找出下一次作業計劃在 sql 中執行的時間 - 實時，即使該作業以前從未執行過
我在這裡有一個非常相似的問題，但該問題/答案的問題在於它不適用於所有情況。例如，對於我剛剛創建但尚未執行的作業。
我有一個函式，可以解決上述問題，主要基於此連結，該連結以 job_id 和 bit 作為參數，並返回上次執行的狀態，以及在任何情況下計劃下次執行的時間。
現在的問題是，當我使用預設參數執行該函式時，每個作業的行數應該與它們上的步驟一樣多，但這並沒有發生，我無法弄清楚原因。
它一定是我在 where 子句中缺少的東西，但在哪裡？
/* ======================================================================= Script : fn_GetJobLastRun Author : Marcelo Miorelli Date : 5-May-2015 Desc : this function returns info about the last time the job @job_id has run -- https://stackoverflow.com/questions/21310616/how-to-get-the-last-run-job-details-in-sql --------------------------------------------------- index to help : --------------------------------------------------- use msdb go -- have a look at what indexes are already there sp_helpindex9 'sysjobhistory' GO -- the new version of the index with included columns to cover for this function CREATE NONCLUSTERED INDEX nc1 ON [dbo].[sysjobhistory] ( [job_id] ASC ) INCLUDE ( step_id, step_name, message, run_status, run_date, run_time, run_duration ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 95 , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , DROP_EXISTING = ON , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] -- the rollback of the index creation CREATE NONCLUSTERED INDEX nc1 ON [dbo].[sysjobhistory] ( [job_id] ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 95 , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , DROP_EXISTING = ON , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] --------------------------------------------------- Usage : --------------------------------------------------- -- get an existing job_id to be used in the example using sp_help_job --exec msdb.dbo.sp_help_job ----@job_id = '6175FB4C-49B9-47EE-A1EF-8606C77EFF4D' DECLARE @job_id UNIQUEIDENTIFIER SELECT @job_id = '6175FB4C-49B9-47EE-A1EF-8606C77EFF4D' --select @job_id = '242070B1-AD60-40DF-8F15-0464C493F38A' SELECT [job_id] ,[JobName] ,[step_id] ,[StepName] ,[RunDate] ,[RunTime] ,[run_duration] ,[ExecutionStatus] ,[MessageGenerated] ,next_scheduled_run_date FROM master.dbo.fn_GetJobLastRun(@job_id,0) SELECT [job_id] ,[JobName] ,[step_id] ,[StepName] ,[RunDate] ,[RunTime] ,[run_duration] ,[ExecutionStatus] ,[MessageGenerated] ,next_scheduled_run_date FROM master.dbo.fn_GetJobLastRun(@job_id,1) SELECT [job_id] ,[JobName] ,[step_id] ,[StepName] ,[RunDate] ,[RunTime] ,[run_duration] ,run_status ,[ExecutionStatus] ,[MessageGenerated] ,next_scheduled_run_date FROM master.dbo.fn_GetJobLastRun(default,default) ======================================================================= History Date Action User Desc ----------------------------------------------------------------------- 30-Mar-2016 created Marcelo Miorelli 25-jul-2016 ammended M.M. when the job has just been created it would not show on the results - fixed bugs - there are still some bugs when I use the function like FROM master.dbo.fn_GetJobLastRun(default,default) - but I cant deal with this right now. ======================================================================= */ SELECT Radhe.job_id ,Radhe.name as JobName ,R2.step_id ,R2.step_name as StepName ,CONVERT(CHAR(10), CAST(STR(R2.run_date,8, 0) AS DATETIME), 111) as RunDate ,STUFF(STUFF(RIGHT('000000' + CAST ( R2.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') as RunTime ,R2.run_duration ,R2.run_status ,R3.next_scheduled_run_date ,CASE COALESCE(R2.run_status,108) WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In Progress' WHEN 108 THEN 'Never Run' ELSE 'Hare Krishna' END as ExecutionStatus ,SUBSTRING(R2.message,1,1024) as MessageGenerated FROM msdb.dbo.sysjobs Radhe LEFT OUTER JOIN msdb.dbo.sysjobhistory R2 ON Radhe.job_id = R2.job_id --Join to pull most recent job activity per job, not job step INNER JOIN ( SELECT TOP ( CASE WHEN @job_id IS NULL THEN 100000 ELSE 1 END ) Radhe.job_id ,Radhe.start_execution_date ,Radhe.stop_execution_date ,Radhe.next_scheduled_run_date FROM msdb.dbo.sysjobactivity Radhe --If no job_id detected, return last run job WHERE Radhe.job_id = COALESCE(@job_id,Radhe.job_id) ORDER BY last_executed_step_date DESC ) R3 --Filter on the most recent job_id ON Radhe.job_id = R3.job_Id WHERE 1=1 AND 1= CASE COALESCE(R2.run_status,108) WHEN 108 THEN 1 ELSE CASE WHEN ( (@show_steps = 0 AND R2.step_id = 0) OR (@show_steps = 1) ) --Filter out job steps that do not fall between start_execution_date and stop_execution_date AND COALESCE(CONVERT(DATETIME, CONVERT(CHAR(8), R2.run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), R2.run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) , R3.start_execution_date ) BETWEEN R3.start_execution_date AND R3.stop_execution_date THEN 1 ELSE 0 END -- CASE END -- CASE GO
實際問題在以下問題上得到了很好的描述和回答：
SQL 代理如何以及何時更新 next_run_date/next_run_time 值？
我發現的一個解決方案是使用以下過程顯示作業的下一個執行時間，即使您剛剛創建它也是如此。
只需將 jobName 作為參數傳遞。
use master go CREATE PROCEDURE [dbo].[sp_ViewJobListing] ( @JobName VARCHAR(255)=NULL ) AS /* http://www.techrepublic.com/blog/the-enterprise-cloud/how-do-i-assign-permissions-to-users-to-see-sql-agent-jobs/ sp_ViewJobListing @jobname = 'ADS Full MetaData Refresh' */ BEGIN IF OBJECT_ID('tempdb..#Results')>0 DROP TABLE #Results CREATE TABLE #Results ( job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, request_source INT NOT NULL, request_source_id SYSNAME COLLATE DATABASE_DEFAULT NULL, running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL ) DECLARE @JobID VARCHAR(100) SELECT TOP 1 @JobID = job_ID FROM msdb.dbo.sysjobs INSERT INTO #Results EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @JobID SELECT s.Name, CASE WHEN s.enabled = 0 THEN 'No' ELSE 'Yes' END AS Enabled, CASE WHEN next_run_date > 0 THEN 'Yes' ELSE 'No' END AS Scheduled, sc.name AS Category, current_step AS CurrentExecutionStep, last_run_date, next_run_date, CASE WHEN xp.running = 0 THEN 'Not Running' ELSE 'Executing...' END AS Status, ISNULL(( SELECT CASE WHEN run_status = 1 THEN 'Succeeded' WHEN run_status = 3 THEN 'Cancelled' WHEN run_status = 0 THEN 'Failed' WHEN run_status IS NULL THEN 'Unknown' END AS LastRunStatus FROM msdb..sysjobhistory sho WHERE sho.job_id = xp.job_id AND sho.instance_id = ( SELECT MAX(instance_id) FROM msdb..sysjobhistory sj (NOLOCK) WHERE sj.job_id = sho.job_id ) ) ,'Unknown') AS LastRunStatus FROM #Results xp INNER JOIN msdb..sysjobs s on xp.job_id = s.job_id INNER JOIN msdb..syscategories sc on s.category_id = sc.category_id WHERE s.Name = ISNULL(@JobName, s.Name) ORDER BY s.Name IF @JobName IS NOT NULL BEGIN CREATE TABLE #JobHistory ( StepID INT, StepName SYSNAME, Message NVARCHAR(1024), RunStatus INT, RunDate INT, RunTime INT, RunDuration INT, operator_emailed NVARCHAR(20), operator_netsent NVARCHAR(20), operator_paged NVARCHAR(20) ) INSERT INTO #JobHistory SELECT sjh.step_id, sjh.step_name, sjh.message, sjh.run_status, sjh.run_date, sjh.run_time, sjh.run_duration, operator_emailed = so1.name, operator_netsent = so2.name, operator_paged = so3.name FROM msdb.dbo.sysjobhistory sjh JOIN msdb.dbo.sysjobs sjj ON sjh.job_id = sjj.job_id LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjh.operator_id_emailed = so1.id) LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjh.operator_id_netsent = so2.id) LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjh.operator_id_paged = so3.id), msdb.dbo.sysjobs sj WHERE sjj.Name = @JobName and (sj.job_id = sjh.job_id) SELECT StepID, StepName, Message, RunDate AS LastRunTime, CASE RunStatus WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry (step only)' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In-progress message' WHEN 5 THEN 'Unknown' END AS RunStatus FROM #JobHistory ORDER BY LastRunTime DESC, StepID ASC END END GO EXECUTE sp_ms_marksystemobject 'sp_ViewJobListing'我還發現，當 jobschedule 表中沒有 next_run_date 時（因為可能需要 20 分鐘才能更新），我可以從表 sysjobactivity 中獲取它，如下面的範例中的部分列表所示：
,[Next_Run_Date] = CASE [jobschedule].[next_run_date] WHEN 0 THEN (select top 1 next_scheduled_run_date from msdb.dbo.sysjobactivity WITh(NOLOCK) where job_id = [jobs].job_id order by run_requested_date desc ) 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