Tuesday, March 5, 2013

Last Successful SQL Server Agent Job Run Date and Time

SQL Server stores SQL Server Agent job history in sysjobhistory table within the msdb database. We can query the table to get the date and time of last successful SQL Server agent job(s) run. The table stores each job step history, where step_id 0 for each job actually give the overall job outcome.

The sysjobhistory table also stores each step's run date and time. However, they are being stored in separate columns, run_date and run_time. Also instead of having date and/or time data type, they have integer data type. But don't worry, there is a system function in msdb database, called agent_datetime, which can be used to convert those run_date and run_time into a datetime data type.

So, to get the date and time of the last successful SQL Server Agent job(s) run, we can use the following script:

WITH jobhistory as (   
   SELECT    job_id,
             run_status,
             last_run_time = max(dbo.agent_datetime(run_date, run_time))
   FROM      msdb.dbo.sysjobhistory
   WHERE     step_id = 0
             AND run_status = 1
   GROUP BY job_id, run_status)

SELECT    j.job_id,
          j.name,
          jh.run_status,
          jh.last_run_time
FROM      msdb.dbo.sysjobs j
          LEFT OUTER JOIN jobhistory jh
          ON j.job_id = jh.job_id
ORDER BY j.name ASC

The script should be pretty straight forward. Basically, first we build a CTE to get the last successful time of each job from the sysjobhistory table. We do this by filtering those SQL Server agent jobs that have run status equal to 1 (success) for job step with id 0. Once we do that, we then create a join of the sysjobs table (which contains information such as the job name), with the CTE.

3 comments:

  1. How would one translate this into Powershell 3.0 & SQL Server 2005?

    I've tried below But get no results.:
    $jb = New-Object "Microsoft.SqlServer.Management.Smo.Agent.Job($srv.JobServer)";

    $jobLastRunDate = $jb.LastRunDate

    #Write-Host $jb.LastRunDate
    $Sheet.Cells.Item($intRow,4) = $jobLastRunDate
    $Sheet.Cells.Item($intRow,4).Interior.ColorIndex = 4




    Thanks,
    Shuaib

    ReplyDelete
    Replies
    1. Hi Shuaib

      One possibility would be using the following:

      [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

      # Connect to the SQL Server instance that has the SQL Agent jobs that you want to query.
      $SqlSvr = new-object Microsoft.SqlServer.Management.Smo.Server("[SQLServerServerInstanceName]")

      # List the SQL Server Agent jobs name and the last run date for each of the job.
      $SqlSvr.JobServer.Jobs | Select Name, LastRunDate

      Hope that helps.

      Thank you
      Lucas

      Delete
  2. hi just a quick tweak to add the runduration so you can get the end time of the job

    WITH jobhistory as (
    SELECT job_id,
    run_status,
    last_run_time = max(dateadd(ss,run_duration % 100 + ROUND((run_duration % 10000) / 100, 0, 0) * 60 + ROUND((run_duration % 1000000) / 10000, 0, 0) * 3600 ,msdb.dbo.agent_datetime(run_date, run_time)))

    FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0
    --AND run_status <> 1
    GROUP BY job_id, run_status)

    ReplyDelete