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.

Monday, September 5, 2011

vwJob

This view is helpful for showing all the jobs that exist on this instance of SQL Server.  Note the very handy first column, NextRun which indicates which scheduled jobs are to be run in the future, or which have already run for this day.

If I ever need to shut down a server, or shut down SQL Agent, I query this view first so I know what jobs are scheduled to run next, or what jobs may not run during the downtime.

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


Use Admin
go

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

CREATE VIEW [dbo].[vwJob]
AS
/*    DATE        AUTHOR            REMARKS
      9/3/11            PPaiva            Initial creation.

     
      DESCRIPTION
            Returns active jobs by next scheduled date (works only on SQL 2005).

      NOTE that since this JOINs to sysJobSchedules, this view
            does not necessarliy show a distinct list of Job Name.

      DEPENDENCIES
            udfFormatTimeWithColons()

      USAGE
            -- The next job to be executed is shown at the top
            SELECT *
            FROM vwJob
            ORDER BY Server, JobEn desc, SchedEn desc, NextRunDateTime

            SELECT *
            FROM vwJob
            ORDER BY Server, JobEn desc, SchedEn desc, OrderByMe, NextRunDateTime


*/

WITH cte (Server, JobEn, SchedEn, NextRunDateTime, Name,
                  Category, SchedName, Next_Run_Date, Next_Run_Time,
                  JobCreated, JobModified, Description, Job_ID)
AS
(
SELECT  Convert(varchar(50), ServerProperty('ServerName')) Server,
            J.Enabled JobEn,
            ss.Enabled SchedEn,
            Convert(varchar(16),
                  CASE WHEN IsNull(JS.Next_Run_Date, 0) = 0 THEN Null
                         ELSE Convert(datetime,
                                          Convert(varchar, JS.Next_Run_Date) +
                                          ' ' + dbo.udfFormatTimeWithColons(Right('000000' + Convert(varchar, JS.Next_Run_Time), 6))
                                                      )
                        END, 120) NextRunDateTime,
            J.Name,
            C.Name Category,
            ss.Name SchedName,
--          Originating_Server OrigServer,
            JS.Next_Run_Date,
            JS.Next_Run_Time,
            J.Date_Created JobCreated,
            J.Date_Modified JobModified,
            J.Description,   
            J.Job_ID
FROM msdb.dbo.sysJobs J
LEFT JOIN msdb.dbo.sysJobSchedules JS
      ON JS.Job_ID = J.Job_ID
LEFT JOIN msdb.dbo.syscategories C
      ON C.Category_ID = J.Category_ID
LEFT JOIN msdb.dbo.sysschedules ss
      ON ss.schedule_ID = js.schedule_ID
)

SELECT CASE WHEN NextRunDateTime Is Null
                        THEN 'Null'
                   WHEN NextRunDateTime < GetDate() - 1
                        THEN 'Past'
                  ELSE 'Future'
            END NextRun,
            *,
            CASE WHEN NextRunDateTime Is Null
                        THEN '2200-01-01'
                   WHEN NextRunDateTime < GetDate() - 1
                        THEN '2100-01-01'
                  ELSE NextRunDateTime
            END OrderByMe
FROM cte




No comments:

Post a Comment