In this article, I present four options for returning SQL Server Agent job history data.

The Options

I’ve included two GUI options and two T-SQL options:

• Option 1: Use the SSMS GUI.
• Option 2: Use the Azure Data Studio GUI (via the SQL Server Agent extension)
• Option 3: Execute the sp_help_jobhistory stored procedure.
• Option 4: Query the sysjobhistory table (and join it with the sysjobs_view view or the sysjobs table).

The SQL Server Agent objects reside in the msdb database, and so the T-SQL options need to be run in that database. You can do that by switching to the msdb database first, or by qualifying the object appropriately (e.g. msdb.dbo.sysjobhistory).

Option 1: Use the SSMS GUI

You can use the SQL Server Management Studio (SSMS) GUI to view job history.

You can do this by expanding the SQL Server Agent node in the Object Explorer, then right-clicking Jobs and selecting View History from the contextual menu:

This opens a new window with the job history of all jobs inside the Log File Viewer:

You can view the history of a single job by deselecting the other jobs in this screen. Alternatively, you can locate that job in the Object Explorer and right-click it from there.

See View SQL Server Agent Job History with SSMS for more details and screenshots.

Option 2: Use the Azure Data Studio GUI

If you use Azure Data Studio, you may not know it, but you also have the option to view SQL Server Agent job history.

The way to do this is via the SQL Server Agent extension.

Here’s what that looks like:

In this screen, we’re looking at the history for a job called BackupKrankyKranesDB.

The job history is listed in the left pane. You can click on each item in the left history pane to display that item’s details in the right pane.

As of this writing, it appears that you can only view the history of a single job at a time.

See View SQL Server Agent Job History with Azure Data Studio for more details and screenshots.

Option 3: The sp_help_jobhistory Stored Procedure

If you prefer (or need) to do your tasks with T-SQL, then the sp_help_jobhistory stored procedure is a quick and easy option for you.

When you call sp_help_jobhistory without any arguments, it returns the history for all jobs. But when you pass the name or ID of a job, it lists out just the history for that one job.

Here’s an example:

EXEC msdb.dbo.sp_help_jobhistory;

Result:

Note that sp_help_jobhistory is located in the msdb database, so you need to ensure that you run it from there. You can do this either by switching to that database (e.g. with USE msdb), or by qualifying the stored procedure with the database and schema (i.e. msdn.dbo.sp_help_jobhistory).

You can get the history of a single job by passing that job’s ID or name as an argument.

Example:

EXEC msdb.dbo.sp_help_jobhistory
	@job_name = 'BackupKrankyKranesDB';

You can also use the @mode parameter to specify whether or not to return all columns in the result set (FULL), or just a summary (SUMMARY).

EXEC msdb.dbo.sp_help_jobhistory
	@job_name = 'BackupKrankyKranesDB',
	@mode = 'FULL';

The default is SUMMARY.

Option 4: The sysjobhistory Table

The sysjobhistory table is the table that stores job history data.

As with any table, you can simply do something like this:

SELECT * FROM msdb.dbo.sysjobhistory;

That will return all columns in the table.

However, this table doesn’t store the job name (or the job’s description, etc). To get that data, you’ll need to join this table with other tables/views, such as the sysjobs_view view or the sysjobs table. That will provide a more complete result set.

Below is a query that you can use to return more complete data.

SELECT jv.name AS Job,
		jh.step_name AS Step,
		msdb.dbo.AGENT_DATETIME(jh.run_date, jh.run_time) AS RunDateTime,
		STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(jh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') AS RunDuration
FROM msdb.dbo.sysjobs_view jv
INNER JOIN msdb.dbo.sysjobhistory jh
ON jv.job_id = jh.job_id
ORDER BY Job, RunDateTime;

Result:

You can add more columns to the SELECT list as required.

If you’re wondering why this query uses a whole bunch of extra stuff, like the AGENT_DATETIME() function, the STUFF() function, RIGHT(), CAST(), and REPLICATE(), it’s because of the way sysjobhistory stores its datetime values.

If I hadn’t used those functions, the datetime values would have been less readable.

In particular, the run_date, run_time and run_duration columns store their data as int values. By default, the data in those columns looks like this:

That can make it hard for some of us humans to read. Especially the run_duration column. Therefore, we used the various functions in the above query to present these columns in a more human-readable format.

You might notice that the sp_help_jobhistory stored procedure suffers the same issue.

Also, I should mention that AGENT_DATE() appears to be an undocumented function.