SQL SERVER – T-SQL Script to Check SQL Server Job History

Yesterday my long time customer from New Zealand asked me during our regular weekly meet of Comprehensive Database Performance Health Check if I know a T-SQL Script to Check SQL Server Job History.

Absolutely, here is the simple script, I use to check all the SQL Server Agent Job History. Please note that while writing this script, I have taken help of the function agent_datetime which I have blogged earlier a few years ago.

Let us see the T-SQL Script:

SELECT jobs.name AS 'JobName',
msdb.dbo.agent_datetime(run_date, run_time) AS 'Run Date Time',
history.run_duration AS 'Duration in Second'
FROM msdb.dbo.sysjobs jobs
INNER JOIN msdb.dbo.sysjobhistory history
ON jobs.job_id = history.job_id
WHERE jobs.enabled = 1 

The script is very simple and it returns details of all the enabled job. You can also remove the last where condition and it will return all the jobs on your system. While doing SQL Server performance tuning, I often ask my client questions about the jobs which are running currently on their server. My experience says there are always few jobs which no one knows why are running on the server and often they negatively impact the performance.

Here are few additional blog posts related to SQL Server Job History:

• SQL SERVER – How to Get SQL Server Agent Properties?
• How to Find Service Account for SQL Server and SQL Server Agent? – Interview Question of the Week #179
• SQL SERVER – SQL Server Agent Not Starting – Failed to Initialize SQL Agent Log (Reason: Access is Denied).
• How to List All the SQL Server Jobs When Agent is Disabled? – Interview Question of the Week #171
• SQL SERVER – SQL Server Agent Missing in SQL Server Management Studio (SSMS)
• SQL SERVER – Execution Failed. See the Maintenance Plan and SQL Server Agent Job History Logs for Details

Reference: Pinal Dave (https://blog.sqlauthority.com)