Introduction
Sometime it can be very difficult for one to find out the sequence of SQL Server Agent Job execution when there are multiple jobs running on the same server. In such scenarios the below TSQL script will come in handy. The script displays last successful SQL Server Agent job run status, data and time.
Execute the below TSQL script to find the Last Successful SQL Server Agent Job Run Status, Date and Time
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
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
In the below snippet you could see the run status and the last successfully execution data and time of each SQL Server Agent Job.
Read more: http://www.mytechmantra.com/LearnSQLServer/SQL-Server-Query-to-Find-Last-Successful-SQL-Server-Agent-Job-Run-Status-Data-and-Time/#ixzz3h4ekvQx9
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook
