How to Display SQL Server Job History
SQL Server developers and database administrators can list SQL Server job history by querying MSDB database. SQL Server MSDB database stores data related with SQL Server Agent service processes like database jobs, SQL Server job scheduling, job history, etc. So it is very easy to list SQL job history if you know the correct MSDB database tables.
In this tutorial, I'll try to show build-in SSMS (SQL Server Management Studio) tools used to display SQL job history and create a Transact-SQL query which can be used as a SQL Server tool for managing job scheduling.
select * from msdb.dbo.sysjobhistory
SQL Server developers can relate the sysjobhistory SQL job history table with sysjobs SQL Server job definitions table over job_id columns.
select
jh.instance_id,
j.job_id,
j.name,
j.enabled,
j.description,
j.category_id,
jh.step_id,
jh.step_name,
jh.sql_severity,
jh.message,
jh.run_status,
jh.run_date,
jh.run_time,
jh.run_duration,
jh.server
from msdb.dbo.sysjobhistory jh
inner join msdb.dbo.sysjobs j on jh.job_id = j.job_id
The SQL Server job history query can be related with other master data tables like syscategories in SQL Server MSDB database. Joining job history with job class table over category_id columns will provide additional grouping information about the SQL Server job
Besides querying and displaying SQL Server job history using T-SQL, SQL Server administrators and database developers can use SQL Server Management Studio tools for this task. First connect to the target SQL Server instance where the SQL Server Agent which runs the sql job resides.
Drill-down SQL Server Agent node. Display sql jobs under the Jobs node.
Right-click on the related SQL Server job you want to display execution history. From the displayed context menu choose View History menu option
This menu selection will display the Log File Viewer SQL Server tool. The log file viewer screen will be listing the execution logs for the selected SQL Server job. Developers and administrators can use this log list as the SQL job history.
You can also get job step details from the same Log File Viewer SQL Server tool.