Easier way to view SQL Server job history
Ever get a timeout error when using the “View History” for a SQL Server Agent job? I find this happens when you have a lot of rows of job history and/or the SQL Server box is getting hit hard. So, I have a stored procedure that I use to display the job history using a couple of system tables that won’t time out on you. Plus the output of this stored procedure is easier to read than the “View History” dialog box. The procedure is below. It takes two parameters: Date, which will show you the job history for that date (and only that date), and Job Name. An example of how to execute it:
sp_ListJobRunHistory ‘5/1/2011’, ‘Daily Feed Load’
Here is the stored procedure:
CREATE PROCEDURE [DBO].[Sp_listjobrunhistory] @dateparam DATETIME, @JobName VARCHAR(100) AS BEGIN SELECT sysjobhistory.server, sysjobs.name AS job_name, CASE sysjobhistory.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' ELSE '???' END AS run_status, Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' + Substring(CONVERT(VARCHAR (8), run_date), 5, 2) + '-' + Substring(CONVERT(VARCHAR( 8), run_date), 7, 2), '') AS [Run DATE], Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':' + Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2 ) + ':' + Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '') AS [Run TIME], Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) + ':' + Substring(CONVERT(VARCHAR(7), run_duration+1000000), 4, 2) + ':' + Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2), '' ) AS [Duration], sysjobhistory.step_id, sysjobhistory.step_name, sysjobhistory.MESSAGE FROM msdb.dbo.sysjobhistory INNER JOIN msdb.dbo.sysjobs ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id WHERE sysjobhistory.run_date = Datepart(yyyy, @dateparam) * 10000 + Datepart(mm, @dateparam) * 100 + Datepart ( dd, @dateparam) AND sysjobs.name = @JobName --remove this line if you want to show all jobs for the specified day ORDER BY instance_id DESC END
Comments
Easier way to view SQL Server job history — No Comments
HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>