SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server Tools Reviews and Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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 job history table 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

SQL Server Management Studio View Job History tool

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.

SQL Server Log File Viewer for job history

You can also get job step details from the same Log File Viewer SQL Server tool.






Related SQL Resources

SQL Server Articles

SQL Server 2016

SQL Server 2014

SQL Server 2012

SQL Server Tools

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems