This topic describes how to view the Microsoft SQL Server Agent job history log using SQL Server Management Studio, Transact-SQL, or SQL Server Management Objects.
To view the job history log
- In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
- Expand SQL Server Agent, and then expand Jobs.
- Right-click a job, and then click View History.
- In the Log File Viewer, view the job history.
- To update the job history, click Refresh. To view fewer rows, click the Filter button and enter filter parameters.
Using Transact-SQL USE msdb ; GO
EXEC dbo.sp_help_jobhistory @job_name = N'<Job Name>' ; GO
T-SQL Script for SQL Server Agent Job History
select
j.name as 'JobName'
,j.date_created
,j.description
,s.step_id as 'Step'
,s.step_name as 'StepName'
,s.last_run_date
,s.last_run_duration
,s.database_name
,msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime' -- function for datetime
((run_duration/100003600 + (run_duration/100)%10060 + run_duration%100 + 31 ) / 60) as 'RunDurationMinutes'
,case h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' END as run_status
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id AND s.step_id = h.step_id AND h.step_id <> 0
where j.enabled = 1 --Only Enabled Jobs
and h.run_date >20200101-- yyyymmdd
--and j.name = 'Job name'
/*
and msdb.dbo.agent_datetime(run_date, run_time)
BETWEEN '12/10/2019' and '12/11/2010' --Uncomment for date range queries
*/
order by JobName, RunDateTime desc
