SQL Server Job History

The following query will give you the job history for a specific job. You will need to find the job_id of the target job before executing and plug it in to the WHERE clause.


--FIND YOUR JOB ID FIRST, plug it in at the bottom
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 = '30B151E5-A422-461C-B118-E856D4BED635'
ORDER BY LastRunDateTime desc

From there you can make charts and stuff. Man I like charts and stuff.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s