locked
Find When SQL Job Was Last Succesful RRS feed

  • Question

  • When I click the Job Activity Monitor, it is only showing data for today (now granted that is about 100 entries).  I want to go through the logs and discover the last time one of these jobs was successful.
    Thursday, May 14, 2015 7:18 PM

Answers

  • Yep that's due to the SQL Server Agent settings on my second screenshot. The history retained is limited. Change those settings to see more history in the future. Just be aware that this will take up more space in your msdb database. 

    • Marked as answer by IndigoMontoya Thursday, May 14, 2015 8:13 PM
    Thursday, May 14, 2015 8:04 PM

All replies

  • There are couple of ways to do it:

    Way #1

    DECLARE @JobName varchar(50) = N'JobName' 
    SELECT TOP (1) 
     CONVERT(DATETIME, RTRIM(run_date))
        + ((run_time / 10000 * 3600) 
        + ((run_time % 10000) / 100 * 60) 
        + (run_time % 10000) % 100) / (86399.9964) AS run_datetime
    FROM msdb.dbo.sysjobhistory
    WHERE job_id IN (SELECT job_id
    				FROM msdb.dbo.sysjobs
    				where name = @JobName)
    AND step_id = 0 
    AND run_status = 1
    ORDER BY [run_date] DESC
    

    Way #2 (Shamelessly copied from here)

    Use msdb
    GO
    
    SELECT 
        SJ.NAME AS [Job Name]
        ,RUN_STATUS AS [Run Status]
        ,MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
    FROM 
        dbo.SYSJOBS SJ 
            LEFT OUTER JOIN dbo.SYSJOBHISTORY JH
        ON SJ.job_id = JH.job_id
            WHERE JH.step_id = 0
                AND jh.run_status = 1
                    GROUP BY SJ.name, JH.run_status 
                        ORDER BY [Last Time Job Ran On] DESC
    GO
    
    

    But the logic will remain same; i.e.

    • Take job id from sysjobs table
    • Search for latest date in sysjobhistory table for successful jobs (i.e. run_status = 1) at its last step [i.e. step_id=0]

    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    Thursday, May 14, 2015 7:54 PM

  • I assume you're looking in job history and the job runs so frequently that you don't have enough history?

    You have a couple of options, firstly you could set the job to alert on certain conditions in the job properties (such as success or failure), here:

    You can also change the amount of history that keep by right-clicking on the SQL Server agent and opening Properties, like this:

    As things currently stand I don't think you'll be able to see when it last successfully ran until you change these settings. 

    Thursday, May 14, 2015 7:54 PM
  • There are couple of ways to do it:

    Way #1

    DECLARE @JobName varchar(50) = N'JobName' 
    SELECT TOP (1) 
     CONVERT(DATETIME, RTRIM(run_date))
        + ((run_time / 10000 * 3600) 
        + ((run_time % 10000) / 100 * 60) 
        + (run_time % 10000) % 100) / (86399.9964) AS run_datetime
    FROM msdb.dbo.sysjobhistory
    WHERE job_id IN (SELECT job_id
    				FROM msdb.dbo.sysjobs
    				where name = @JobName)
    AND step_id = 0 
    AND run_status = 1
    ORDER BY [run_date] DESC


    This did not return any results.  So that means it was so long ago?

    But if I test this on a job that was successful, it doesn't return anything either.  (Successfully completed today)

    Thursday, May 14, 2015 8:01 PM

  • I assume you're looking in job history and the job runs so frequently that you don't have enough history?

    You have a couple of options, firstly you could set the job to alert on certain conditions in the job properties (such as success or failure), here:

    You can also change the amount of history that keep by right-clicking on the SQL Server agent and opening Properties, like this:

    As things currently stand I don't think you'll be able to see when it last successfully ran until you change these settings. 

    Yes, that is exactly where I am looking.  And looks like I will not know when the last time it was successful as it is not displaying at all.
    Thursday, May 14, 2015 8:03 PM
  • Yep that's due to the SQL Server Agent settings on my second screenshot. The history retained is limited. Change those settings to see more history in the future. Just be aware that this will take up more space in your msdb database. 

    • Marked as answer by IndigoMontoya Thursday, May 14, 2015 8:13 PM
    Thursday, May 14, 2015 8:04 PM