How to find SQL Server Job history

 

How to View SQL Server Agent Job History

When troubleshooting or monitoring scheduled tasks, it's important to be able to review the execution history of SQL Server Agent jobs. This article outlines multiple methods to view job history using SQL Server Management Studio (SSMS), Transact-SQL (T-SQL), and system views in the msdb database.

---

Method 1: Using SQL Server Management Studio (SSMS)

You can access job history directly from the SQL Server Management Studio interface:

1. Open Object Explorer and connect to your SQL Server instance.
2. Expand the SQL Server Agent node.
3. Expand Jobs.
4. Right-click the job you want to investigate and select View History.
5. The Log File Viewer will display execution details.
   Use Refresh to reload the history or click Filter to narrow down the results.

---

Method 2: Using Transact-SQL

For quick access via T-SQL, run the following:

USE msdb;
GO

EXEC dbo.sp_help_jobhistory @job_name = N'';
GO

Replace <Job Name> with the name of your SQL Server Agent job to retrieve its execution history.

---

Method 3: Detailed T-SQL Script for Job History

To get a more comprehensive view, including step-level details and execution outcomes, use the query below:

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(h.run_date, h.run_time) AS RunDateTime,
    ((run_duration / 10000 * 3600 + (run_duration / 100) % 100 * 60 + 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 RunStatus
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
  AND h.run_date > 20200101
ORDER BY JobName, RunDateTime DESC;

💡 Note: You can uncomment the filters for job name or date range as needed.

---

This approach offers flexibility whether you prefer a visual interface or a script-based method. Monitoring job history is essential for diagnosing failures, optimizing performance, and ensuring scheduled processes complete as intended.