Checking SQL Server Job History by Script
Mainly msdb.dbo.sysjobs and msdb.dbo.sysjobhistory contains all major information regarding the SQL Server job and its running status and history. We may directly query both objects and get the details or we may use SSMS and view agent job history.
Solution
Here is a simple TSQL to simplify the work:
Query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
SELECT [job].[job_id] AS [JobID], [job].[name] AS [JobName], CASE WHEN [jh].[run_date] IS NULL OR [jh].[run_time] IS NULL THEN NULL ELSE CAST(CAST([jh].[run_date] AS CHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST([jh].[run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) END AS [LastRunDateTime], CASE [jh].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' -- In Progress END AS [LastRunStatus], STUFF(STUFF(RIGHT('000000' + CAST([jh].[run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)], [jh].[message] AS [LastRunStatusMessage] FROM [msdb].[dbo].[sysjobs] AS [job] LEFT JOIN ( SELECT [job_id], [run_date], [run_time], [run_status], [run_duration], [message], ROW_NUMBER() OVER (PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC) AS RowNumber FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0 ) AS [jh] ON [job].[job_id] = [jh].[job_id] --WHERE job.job_id = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --You can write the selected Job ID information here. ORDER BY LastRunDateTime DESC; |
Steps
To run a script on a Microsoft SQL Server using SSMS.
- Open Microsoft SQL Server Management Studio
- Select [New Query] from the toolbar
- Copy the ‘Example Query’
- Select the database to run the query against
- Paste the ‘Example Query’ into the query window
- Click the [Execute] button
Result
