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