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.

Tuesday, July 28, 2015

udfFormatJobNextRunDate - Properly format a job's next run date/time

Scenario
You want to quickly determine the next jobs to be executed on a busy server.  Perhaps you need to bring the server down and you want to ensure you don't miss any important jobs.  Or you are managing resources for the next couple of hours and you need to know which jobs to disable.

1st Solution
Your first approach is to query sysjobs and sysschedules.

-- Shows list of jobs and when they next run
SELECT  j.name JobName,
            j.Enabled,
            j.Description,
            s.next_run_date,
            s.next_run_time
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobschedules s
      ON s.job_id = j.job_id
ORDER BY next_run_date desc, next_run_time desc

Alas, while the above query gets you everything you need, the next TIME for when a job is scheduled to run is formatted poorly.  Therefore the ordering is incorrect.

Note the poorly formatted time below.  Ordering by next_run_time will not yield correct results.  I added an additional column showing what the correctly formatted date/time should be.









Solution for formatting next run date and time of jobs using a user-defined function

Use Admin
-- Use master

IF Object_ID('dbo.udfFormatJobNextRunDate') Is Not Null
      DROP FUNCTION dbo.udfFormatJobNextRunDate
go

CREATE FUNCTION dbo.udfFormatJobNextRunDate(
      @NextRunDateTEXT varchar(8),
      @NextRunTimeTEXT varchar(6)
      )
RETURNS Datetime
AS
/*    DATE        AUTHOR            REMARKS
      10/19/15    PPaiva            Initial creation


      DESCRIPTION
            Formats the text columns Next_Run_Date and Next_Run_Time
                  in sysJobSchedules to a datetime format.

            TIME formatting
                   Input:  4000
                  Output:  00:40:00

      USAGE
            SELECT  *, 
              dbo.udfFormatJobNextRunDate(next_run_date, next_run_time) ProperlyFormattedDatetime
            FROM msdb.dbo.sysJobSchedules
            ORDER BY len(next_run_time)
           
            SELECT dbo.udfFormatJobNextRunDate('20150401', '300')
            SELECT dbo.udfFormatJobNextRunDate('20150401', '2300')
            SELECT dbo.udfFormatJobNextRunDate('20150401', '12300')
            SELECT dbo.udfFormatJobNextRunDate('20150401', '112300')

*/
BEGIN
      DECLARE @Out datetime,
                  @NextRunTimeWithColons varchar(8),
                  @LenTime int
     
                 
      -- Pad left with zeros
      SET @LenTime = Len(@NextRunTimeTEXT)
      SET @NextRunTimeTEXT = REPLICATE('0', 6 - @LenTime) + @NextRunTimeTEXT


      -- Add colons.  Change 005000 to 00:50:00
      SET @NextRunTimeWithColons = Left(@NextRunTimeTEXT, 2)
                  + ':'
                  +     Substring(@NextRunTimeTEXT, 3, 2)
                  + ':'
                  +     Right(@NextRunTimeTEXT, 2)


      -- Concatenate date and time
      SET @Out = CASE WHEN IsNull(@NextRunDateTEXT, 0) = 0 THEN Null
                              ELSE Convert(datetime, @NextRunDateTEXT + ' ' + @NextRunTimeWithColons
                                                )
                                    END


      RETURN @Out

END

2nd Solution
Now simply add the new function to the initial query.

-- Shows list of jobs and when they next run (modified with solution)
-- Now jobs are correctly order by next run date/time
SELECT  j.name JobName,
            j.Enabled,
            j.Description,
            s.next_run_date,
            s.next_run_time,
            dbo.udfFormatJobNextRunDate(next_run_date, next_run_time) CorrectlyFormattedDatetime
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobschedules s
      ON s.job_id = j.job_id
ORDER BY 5 desc, 6 desc



No comments:

Post a Comment