SQL Server 2008（SSMS）：查找作业的下一个运行date

在msdb数据库中运行sp_help_job。 next_run_date和next_run_time列具有您正在查找的值。

这会得到你的工作名称和下一个运行date/时间的单行结果集。

DECLARE @JobName sysname SET @JobName='Query Tool Daily Routines' SELECT JobName, MAX(NextRunTime) as NextRunTime FROM ( SELECT j.name as JobName, cast( CONVERT(CHAR(8), next_run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ':'), 3, 0, ':') as datetime) as NextRunTime FROM msdb.dbo.sysjobs j join msdb.dbo.sysjobschedules s on j.job_id = s.job_id and j.name=@JobName ) t1 group by JobName 

您当然可以摆脱DECLARE和SET，并将其包含在内部查询的连接中。

您可以使用此过程来获取所有作业的列表，包括最近3次运行时间和平均持续时间：

 CREATE PROCEDURE Job_Help @sResultTableName varchar(128) = NULL, @sDailyTableName varchar(128) = NULL, @lUniqueId int = NULL, @bEnabled bit = NULL AS /******************* Variables *********************************************************************/ DECLARE @iErrorCode int DECLARE @sSql nvarchar(4000) SET @iErrorCode = @@ERROR /******************* Verify parameters *********************************************************************/ IF @sResultTableName IS NULL RETURN IF @sDailyTableName IS NULL RETURN IF @lUniqueId IS NULL RETURN /************************************************************************************************************/ -- initializes the real temp table name SET @sResultTableName = LTRIM (RTRIM (@sResultTableName)) + CONVERT (varchar(20), CONVERT (decimal(20,0), @lUniqueId)) SET @sDailyTableName = LTRIM (RTRIM (@sDailyTableName)) + CONVERT (varchar(20), CONVERT (decimal(20,0), @lUniqueId)) /************************************************************************************************************/ IF @iErrorCode = 0 BEGIN SET @sSql = N'IF NOT EXISTS( SELECT name ' + char(13) + ' FROM tempdb..sysobjects ' + char(13) + ' WHERE name = N''' + @sResultTableName + '''' + char(13) + ' AND type = ''U'') ' + char(13) + ' CREATE TABLE ' + @sResultTableName + ' ( ' + char(13) + ' job_id uniqueidentifier NOT NULL, ' + char(13) + ' sJobName sysname NOT NULL, ' + char(13) + ' bEnabled bit NOT NULL, ' + char(13) + ' dtCreated datetime NULL, ' + char(13) + ' dtModified datetime NULL, ' + char(13) + ' dtNextRun datetime NULL, ' + char(13) + ' dtPreviousRunStart1 datetime NULL, ' + char(13) + ' dtPreviousRunEnd1 datetime NULL, ' + char(13) + ' sDuration1 varchar(10) NULL, ' + char(13) + ' dtPreviousRunStart2 datetime NULL, ' + char(13) + ' dtPreviousRunEnd2 datetime NULL, ' + char(13) + ' sDuration2 varchar(10) NULL, ' + char(13) + ' dtPreviousRunStart3 datetime NULL, ' + char(13) + ' dtPreviousRunEnd3 datetime NULL, ' + char(13) + ' sDuration3 varchar(10) NULL, ' + char(13) + ' sAvgDuration varchar(10) NULL, ' + char(13) + ' iDuration1 int NULL, ' + char(13) + ' iDuration2 int NULL, ' + char(13) + ' iDuration3 int NULL, ' + char(13) + ' iTempAvgDuration int NULL) ' + char(13) + 'ELSE ' + char(13) + ' TRUNCATE TABLE ' + @sResultTableName EXEC sp_executesql @sSql SET @iErrorCode = @@ERROR END -- IF @iErrorCode = 0 /*********************************************************************************************************************/ IF @iErrorCode = 0 BEGIN SET @sSql = N'INSERT INTO ' + @sResultTableName + ' ( ' + char(13) + ' job_id, ' + char(13) + ' sJobName, ' + char(13) + ' bEnabled, ' + char(13) + ' dtCreated, ' + char(13) + ' dtModified, ' + char(13) + ' dtNextRun) ' + char(13) + ' SELECT DISTINCT J.job_id, ' + char(13) + ' J.name, ' + char(13) + ' J.enabled, ' + char(13) + ' J.date_created, ' + char(13) + ' J.date_modified, ' + char(13) + ' CASE WHEN S.next_run_date = 0 THEN 0 ELSE convert (smalldatetime, substring (convert (varchar(10), S.next_run_date), 1, 4) + ''/'' + substring (convert (varchar(10), S.next_run_date), 5, 2) + ''/'' + substring (convert (varchar(10), S.next_run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), S.next_run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), S.next_run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), S.next_run_time), 6), 5, 2)) END ' + char(13) + ' FROM msdb..sysjobs J INNER JOIN msdb..sysjobschedules S ' + char(13) + ' ON J.job_id = S.job_id ' + char(13) IF NOT @bEnabled IS NULL SET @sSql = @sSql + ' WHERE J.enabled = @bEnabled ' EXEC sp_executesql @sSql, N'@bEnabled bit', @bEnabled SET @iErrorCode = @@ERROR END -- IF @iErrorCode = 0 IF @iErrorCode = 0 BEGIN -- dtPreviousRunStart1, dtPreviousRunEnd1, iDuration1 SET @sSql = N'UPDATE J ' + char(13) + ' SET dtPreviousRunStart1 = run_date, ' + char(13) + ' dtPreviousRunEnd1 = CONVERT (datetime, DATEADD (second, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2)), DATEADD (minute, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2)), DATEADD (hour, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2)), CONVERT (datetime, run_date))))), ' + char(13) + ' sDuration1 = CASE WHEN run_duration IS NULL THEN NULL ELSE substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2) END, ' + char(13) + ' iDuration1 = ISNULL (run_duration, 0)' + char(13) + ' FROM ' + @sResultTableName + ' J INNER JOIN ( ' + char(13) + ' SELECT A.job_id, ' + char(13) + ' A.run_date, ' + char(13) + ' A.run_duration ' + char(13) + ' FROM ( ' + char(13) + ' SELECT job_id, ' + char(13) + ' convert (smalldatetime, substring (convert (varchar(10), run_date), 1, 4) + ''/'' + substring (convert (varchar(10), run_date), 5, 2) + ''/'' + substring (convert (varchar(10), run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 5, 2)) run_date, ' + char(13) + ' run_duration ' + char(13) + ' FROM msdb..sysjobhistory ' + char(13) + ' WHERE step_id = 0 ' + char(13) + ' AND run_status = 1 ' + char(13) + ' ) A INNER JOIN ( ' + char(13) + ' SELECT job_id, ' + char(13) + ' MAX (convert (smalldatetime, substring (convert (varchar(10), run_date), 1, 4) + ''/'' + substring (convert (varchar(10), run_date), 5, 2) + ''/'' + substring (convert (varchar(10), run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 5, 2))) run_date ' + char(13) + ' FROM msdb..sysjobhistory ' + char(13) + ' WHERE step_id = 0 ' + char(13) + ' AND run_status = 1 ' + char(13) + ' GROUP BY job_id ' + char(13) + ' ) B ' + char(13) + ' ON A.job_id = B.job_id ' + char(13) + ' AND A.run_date = B.run_date ' + char(13) + ' ) C ' + char(13) + ' ON J.job_id = C.job_id ' EXEC sp_executesql @sSql SET @iErrorCode = @@ERROR END -- IF @iErrorCode = 0 IF @iErrorCode = 0 BEGIN -- dtPreviousRunStart2, dtPreviousRunEnd2, iDuration2 SET @sSql = N'UPDATE J ' + char(13) + ' SET dtPreviousRunStart2 = run_date, ' + char(13) + ' dtPreviousRunEnd2 = CONVERT (datetime, DATEADD (second, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2)), DATEADD (minute, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2)), DATEADD (hour, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2)), CONVERT (datetime, run_date))))), ' + char(13) + ' sDuration2 = CASE WHEN run_duration IS NULL THEN NULL ELSE substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2) END, ' + char(13) + ' iDuration2 = ISNULL (run_duration, 0)' + char(13) + ' FROM ' + @sResultTableName + ' J INNER JOIN ( ' + char(13) + ' SELECT A.job_id, ' + char(13) + ' A.run_date, ' + char(13) + ' A.run_duration ' + char(13) + ' FROM ( ' + char(13) + ' SELECT H1.job_id, ' + char(13) + ' convert (smalldatetime, substring (convert (varchar(10), H1.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H1.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H1.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 5, 2)) run_date, ' + char(13) + ' H1.run_duration ' + char(13) + ' FROM msdb..sysjobhistory H1 ' + char(13) + ' WHERE H1.step_id = 0 ' + char(13) + ' AND H1.run_status = 1 ' + char(13) + ' ) A INNER JOIN ( ' + char(13) + ' SELECT H2.job_id, ' + char(13) + ' MAX (convert (smalldatetime, substring (convert (varchar(10), H2.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H2.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H2.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 5, 2))) run_date ' + char(13) + ' FROM msdb..sysjobhistory H2 INNER JOIN ' + @sResultTableName + ' J2 ' + char(13) + ' ON H2.job_id = J2.job_id ' + char(13) + ' WHERE H2.step_id = 0 ' + char(13) + ' AND H2.run_status = 1 ' + char(13) + ' AND convert (smalldatetime, substring (convert (varchar(10), H2.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H2.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H2.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 5, 2)) < J2.dtPreviousRunStart1 ' + char(13) + ' GROUP BY H2.job_id ' + char(13) + ' ) B ' + char(13) + ' ON A.job_id = B.job_id ' + char(13) + ' AND A.run_date = B.run_date ' + char(13) + ' ) C ' + char(13) + ' ON J.job_id = C.job_id ' EXEC sp_executesql @sSql SET @iErrorCode = @@ERROR END -- IF @iErrorCode = 0 IF @iErrorCode = 0 BEGIN -- dtPreviousRunStart3, dtPreviousRunEnd3, iDuration3 SET @sSql = N'UPDATE J ' + char(13) + ' SET dtPreviousRunStart3 = run_date, ' + char(13) + ' dtPreviousRunEnd3 = CONVERT (datetime, DATEADD (second, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2)), DATEADD (minute, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2)), DATEADD (hour, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2)), CONVERT (datetime, run_date))))), ' + char(13) + ' sDuration3 = CASE WHEN run_duration IS NULL THEN NULL ELSE substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2) END, ' + char(13) + ' iDuration3 = ISNULL (run_duration, 0)' + char(13) + ' FROM ' + @sResultTableName + ' J INNER JOIN ( ' + char(13) + ' SELECT A.job_id, ' + char(13) + ' A.run_date, ' + char(13) + ' A.run_duration ' + char(13) + ' FROM ( ' + char(13) + ' SELECT H1.job_id, ' + char(13) + ' convert (smalldatetime, substring (convert (varchar(10), H1.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H1.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H1.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 5, 2)) run_date, ' + char(13) + ' H1.run_duration ' + char(13) + ' FROM msdb..sysjobhistory H1 ' + char(13) + ' WHERE H1.step_id = 0 ' + char(13) + ' AND H1.run_status = 1 ' + char(13) + ' ) A INNER JOIN ( ' + char(13) + ' SELECT H2.job_id, ' + char(13) + ' MAX (convert (smalldatetime, substring (convert (varchar(10), H2.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H2.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H2.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 5, 2))) run_date ' + char(13) + ' FROM msdb..sysjobhistory H2 INNER JOIN ' + @sResultTableName + ' J2 ' + char(13) + ' ON H2.job_id = J2.job_id ' + char(13) + ' WHERE H2.step_id = 0 ' + char(13) + ' AND H2.run_status = 1 ' + char(13) + ' AND convert (smalldatetime, substring (convert (varchar(10), H2.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H2.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H2.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 5, 2)) < J2.dtPreviousRunStart2 ' + char(13) + ' GROUP BY H2.job_id ' + char(13) + ' ) B ' + char(13) + ' ON A.job_id = B.job_id ' + char(13) + ' AND A.run_date = B.run_date ' + char(13) + ' ) C ' + char(13) + ' ON J.job_id = C.job_id ' EXEC sp_executesql @sSql SET @iErrorCode = @@ERROR END -- IF @iErrorCode = 0 IF @iErrorCode = 0 BEGIN -- updates iAvgDuration SET @sSql = N'UPDATE ' + @sResultTableName + char(13) + ' SET iTempAvgDuration = ( convert (int, substring (sDuration1, 1, 2) * 3600) + convert (int, substring (sDuration1, 4, 2) * 60) + convert (int, substring (sDuration1, 7, 2)) + ' + char(13) + ' convert (int, substring (sDuration2, 1, 2) * 3600) + convert (int, substring (sDuration2, 4, 2) * 60) + convert (int, substring (sDuration2, 7, 2)) + ' + char(13) + ' convert (int, substring (sDuration3, 1, 2) * 3600) + convert (int, substring (sDuration3, 4, 2) * 60) + convert (int, substring (sDuration3, 7, 2))) / 3 ' EXEC sp_executesql @sSql SET @iErrorCode = @@ERROR END -- IF @iErrorCode = 0 IF @iErrorCode = 0 BEGIN -- updates sAvgDuration SET @sSql = N'UPDATE ' + @sResultTableName + char(13) + ' SET sAvgDuration = RIGHT (''00'' + CONVERT (varchar(10), iTempAvgDuration / 3600), 2) + '':'' + ' + char(13) + ' RIGHT (''00'' + CONVERT (varchar(10), (iTempAvgDuration - (iTempAvgDuration / 3600) * 3600) / 60), 2) + '':'' + ' + char(13) + ' RIGHT (''00'' + CONVERT (varchar(10), (iTempAvgDuration - (iTempAvgDuration / 3600) * 3600) - ((iTempAvgDuration - (iTempAvgDuration / 3600) * 3600) / 60 * 60)), 2) ' EXEC sp_executesql @sSql SET @iErrorCode = @@ERROR END -- IF @iErrorCode = 0 

因为我爱我一些powershell：

 $server = new-object microsoft.sqlserver.management.smo.server 'yourinstance'; $agent = $server.jobserver; $job = $agent.jobs['your job name here']; $job.nextrundate; 

对于在这个旧线程中运行的googlers，“作业活动监视器”会返回许多与sp_help_jobs相同的信息。