About Me

Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present. 517 Lafarge Ave. Louisville, CO 80027 7667 Dyer Road Louisville, CO 80027

Friday, September 9, 2011


This view shows the job history, using a udf to nicely format the run date/times.

Examples of columns in msdb.dbo.sysJobHistory in the “unfriendly” format:
run_date             run_time
20110830             162812

The unused columns from the two tables are commented out, so that if you ever have a need to show them it is easy to insert them into the active part of the query.

This view has a dependency on this object which is easy to install:

Use Admin

IF object_id('dbo.vwJobHistory') Is Not Null
      DROP VIEW dbo.vwJobHistory

CREATE VIEW [dbo].[vwJobHistory]
/*    DATE        AUTHOR            REMARKS
      9/7/11           PPaiva            Initial creation.
      SELECT *
      FROM vwJobHistory
      ORDER BY RunDateTime desc

      SELECT *
      FROM vwJobHistory
      WHERE Status <> 'Succeeded'
      ORDER BY RunDateTime desc


SELECT CASE WHEN Run_Status = 0 THEN 'Failed'
                  WHEN Run_Status = 1 THEN 'Succeeded'
                  WHEN Run_Status = 2 THEN 'Retry'
                  WHEN Run_Status = 3 THEN 'Canceled'
                  WHEN Run_Status = 4 THEN 'In Progress'   
                  ELSE 'Undefined Status in View'
            END Status,
                  CASE WHEN IsNull(Run_Date, 0) = 0 THEN Null
                         ELSE Convert(datetime,
                                          Convert(varchar, Run_Date) +
                                          ' ' + dbo.udfFormatTimeWithColons(Right('000000' + Convert(varchar, Run_Time), 6))
                        END, 120) RunDateTime,
            Convert(decimal(8, 1), run_duration/60.) Mins,
            Run_Duration Secs,
            step_id, step_name, server,
            --sql_message_id, sql_severity, run_status, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted
FROM msdb.dbo.sysJobHistory jh
JOIN msdb.dbo.sysJobs j
            ON j.job_id = jh.job_id

No comments:

Post a Comment