SQL Server – How to Find Next Scheduled Job Run Time in Real Time

jobsscheduled-taskssql serversql server 2014sql-server-2005

I have a very similar question here but the problem with that question/ answer is that it does not work for all situations. for example, for the jobs that I have just created and have not been run as yet.

I have a function, that fixes the problem above,
mostly based on this link that takes a job_id and a bit as parameter, and returns me the status of the last run, and when it is scheduled to run next time
in any situation.

the problem now is when I run the function with the default parameters, each job should have as many lines as there are steps on them, but that is not happening and I haven't been able to figure it out why.

it must be something I am missing in my where clause but 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

Best Answer

the actual problem is well described and answered on the question below:

How and when does SQL Agent update next_run_date/next_run_time values?

one solution I found is to use the procedure below shows the next running time of a job, even if you have just created it.

just pass the jobName as parameter.

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'

I have also found that when the next_run_date is not available from the jobschedule table (because it might take up to 20 min to update), I can get it from the table sysjobactivity, as you can see on the partial list from an example below:

,[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
Related Question