Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.

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

No comments.

Leave a Comment

Please register or log in to leave a comment.