Recently I received a request to gather run-time information about a particular SQL scheduled job. They wanted to know how long a specific job was running. I started digging through the msdb.sysjobhistory table and found some strange values for the columns. After searching the Internet I wrote this query that fit my needs:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT j.[name] AS [Job Name], [msdb].[dbo].[agent_datetime]([run_date], [run_time]) AS [Run DateTime], (([run_duration]/10000*3600 + ([run_duration]/100)%100*60 + [run_duration]%100 + 31 ) / 60) AS [Run Duration (Minutes)] FROM [msdb].[dbo].[sysjobs] j INNER JOIN [msdb].[dbo].[sysjobhistory] h ON j.[job_id] = h.[job_id] WHERE j.[name] = 'JOB NAME' AND [step_id] = 0 --Only Job Outcome Results ORDER BY [Job Name], [Run DateTime] DESC |