0

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.