To check SQL Server Agent Job History you can use the Log File Viewer from SQL Server Management Studio.
To Open Log File Viewer,
1. Expand Server Node > 2. Expand SQL Server Agent > 3. Expand Jobs > 4. Right click on the Job and 5. Select "View History" as shown in the screen shot below:
Alternatively, you can also use below T-SQL code to check Job History:
SELECT [JobName] = JOB.name,
[Step] = HIST.step_id,
[StepName] = HIST.step_name,
[Message] = HIST.message,
[Status] = CASE WHEN HIST.run_status = 0 THEN 'Failed'
WHEN HIST.run_status = 1 THEN 'Succeeded'
WHEN HIST.run_status = 2 THEN 'Retry'
WHEN HIST.run_status = 3 THEN 'Canceled'
END,
[RunDate] = HIST.run_date,
[RunTime] = HIST.run_time,
[Duration] = HIST.run_duration
FROM sysjobs JOB
INNER JOIN sysjobhistory HIST ON HIST.job_id = JOB.job_id
/* WHERE JOB.name = 'Job1' */
ORDER BY HIST.run_date, HIST.run_time
Output:
JobName Step StepName Message Status RunDate RunTime Duration
Job1 1 Step1 Executed a.. Succeeded 20120416 173935 10
Job1 0 (Job outcome) The job su.. Succeeded 20120416 173935 10
Job2 1 Step1 Executed a.. Succeeded 20120416 174037 10
Job2 0 (Job outcome) The job su.. Succeeded 20120416 174037 10
Check dbo.sysjobs and dbo.sysjobhistory on BOL for more information.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Filed under: Catalog Views, Management Studio, SQL Agent, SQLServer