About Me

My photo
Colorado
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.

Friday, September 9, 2011

vwJobHistory

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:
        udfFormatTimeWithColons

Use Admin
go

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

CREATE VIEW [dbo].[vwJobHistory]
AS
/*    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,
            j.Name,
            --Run_Date,
            --Run_Time,
            Convert(varchar(16),
                  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,
            message,
            jh.job_id,
            instance_id
            --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