I would like to know if there is a way to get the next time a job is supposed to be running in SQL Server 2008, using a T-SQL query or even in SSMS if possible, without having to consult all the schedules for all the jobs.
Thank you
Asked
Active
Viewed 5,229 times
5 Answers
6
Run sp_help_job in the msdb database. The next_run_date and next_run_time columns have the values you are looking for.
mrdenny
- 27,174
- 4
- 41
- 69
1
Because I love me some powershell:
$server = new-object microsoft.sqlserver.management.smo.server 'yourinstance';
$agent = $server.jobserver;
$job = $agent.jobs['your job name here'];
$job.nextrundate;
Ben Thul
- 3,024
- 17
- 24
0
This'll get you a single row result set with your job name and the next run date/time.
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
You can of course get rid of the DECLARE and SET and just include it in the join of the inner query.
squillman
- 37,883
- 12
- 92
- 146
0
You can use this procedure, to get a list for all jobs including 3 last running time, and average duration:
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
Michael Hampton
- 244,070
- 43
- 506
- 972
0
FYI for googlers that run across this old thread, the Job Activity Monitor returns a lot of the same information as sp_help_jobs.
Will
- 101
- 1