Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2008
    Posts
    10

    Unhappy Unanswered: SQL T-SQl query (need help)

    I have the following script, which lists a lot of information regarding my Agent jobs on a server:


    SELECT DISTINCT substring(SYSJOBS.name,1,100) AS [Job Name], SYSJOBSTEPS.step_name AS JobStep,
    SYSCATEGORIES.name AS category, SYSJOBS.description as JobDescription, SYSJOBS.date_created as CreateDate,
    'Enabled'=case
    WHEN SYSSCHEDULES.enabled = 0 THEN 'DISABLED'
    WHEN SYSSCHEDULES.enabled = 1 THEN 'ENABLED'
    end,
    -- substring(SYSSCHEDULES.name,1,30) AS [Name of the schedule],
    'Job Frequency '=case
    WHEN SYSSCHEDULES.freq_type = 1 THEN 'ONCE'
    WHEN SYSSCHEDULES.freq_type = 4 THEN 'DAILY'
    WHEN SYSSCHEDULES.freq_type = 8 THEN 'WEEKLY'
    WHEN SYSSCHEDULES.freq_type = 16 THEN 'Monthly'
    WHEN SYSSCHEDULES.freq_type = 32 THEN 'MONTHLY RELATIVE'
    WHEN SYSSCHEDULES.freq_type = 32 THEN 'START AUTOMATICALLY WHEN SQL AGENT STARTS'
    END,

    'Days jobs run'=case
    WHEN SYSSCHEDULES.[freq_interval] = 1 THEN ' SUNDAY'
    WHEN SYSSCHEDULES.[freq_interval] = 2 THEN ' MONDAY'
    WHEN SYSSCHEDULES.[freq_interval] = 3 THEN ' TUESDAY'
    WHEN SYSSCHEDULES.[freq_interval] = 4 THEN ' WEDNESDAY'
    WHEN SYSSCHEDULES.[freq_interval] = 5 THEN ' THURSDAY'
    WHEN SYSSCHEDULES.[freq_interval] = 6 THEN ' FRIDAY'
    WHEN SYSSCHEDULES.[freq_interval] = 7 THEN ' SATURDAY'
    WHEN SYSSCHEDULES.[freq_interval] = 8 THEN ' DAILY'
    WHEN SYSSCHEDULES.[freq_interval] = 9 THEN ' WEEKLY'
    WHEN SYSSCHEDULES.[freq_interval] = 10 THEN 'WEEKEND'
    WHEN SYSSCHEDULES.[freq_interval] = 62 THEN 'MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY'
    WHEN SYSSCHEDULES.[freq_interval] = 64 THEN 'SATURDAY'
    WHEN SYSSCHEDULES.[freq_interval] = 65 THEN 'SATURDAY, SUNDAY'
    WHEN SYSSCHEDULES.[freq_interval] = 126 THEN 'MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY'
    END,

    'INTERVAL TYPE'=case
    WHEN SYSSCHEDULES.freq_subday_type = 1 THEN 'At the specified time'
    WHEN SYSSCHEDULES.freq_subday_type = 2 THEN 'Seconds'
    WHEN SYSSCHEDULES.freq_subday_type = 4 THEN 'Minutes'
    WHEN SYSSCHEDULES.freq_subday_type = 8 THEN 'Hours'
    END,
    cast(cast(SYSSCHEDULES.active_start_date as varchar(15)) as datetime) as StartDate,
    cast(cast(SYSSCHEDULES.active_end_date as varchar(15)) as datetime) as EndDate,
    Stuff(Stuff(right('000000'+Cast(SYSJOBSCHEDULES.ne xt_run_time as Varchar),6),3,0,':'),6,0,':') as Run_Time


    FROM msdb..sysjobs SYSJOBS
    INNER JOIN msdb..sysjobhistory SYSJOBHISTORY ON SYSJOBHISTORY.job_id = SYSJOBS.job_id
    INNER JOIN msdb..sysJobschedules SYSJOBSCHEDULES ON SYSJOBSCHEDULES.job_id = SYSJOBS.job_id
    INNER JOIN msdb..SysSchedules SYSSCHEDULES ON SYSSCHEDULES.Schedule_id = SYSJOBSCHEDULES.Schedule_id
    INNER JOIN msdb..sysjobsteps SYSJOBSTEPS ON SYSJOBSTEPS.job_id = SYSJOBS.job_id
    INNER JOIN msdb..syscategories SYSCATEGORIES ON SYSCATEGORIES.category_id = SYSJOBS.category_id



    This work's fine on SQL 2005 Sp 2, however I would like to have it tell me at what intervals it run at (every 20 mins between 6:00 – 00:00 a, basically I would get this kind of info for a job


    JOB NAME =DBA- Backup ***** logs
    STEP NAME = Log backup for *****
    CATEGORY = DBA Admin
    ENABLED = Yes
    JOB FREQUENCY = Weekly
    ON WHAT DAYS = Mon, Tue, Wed, Thurs, Fri
    INTERVAL TYPE = Every Hour
    EXECUTION TIME = 8:00 – 19:00
    DESCRIPTION = This job backs up the log for *******

    I would be very gratefully, as I just can seem to do it.

    Thanks
    Last edited by MCSC1821; 08-21-09 at 04:22.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    code coming up:
    Code:
       code coming up...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...actually I decided to rewrite it completely, because your 'Days jobs run' column will not return a correct value under all circumstances.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    ...quick attempt...

    I am sure someone can come up with a better solution, but here's my take:
    Code:
    if object_id('dbo.fn__Suffix') is not null
       drop function dbo.fn__Suffix
    go
    create function dbo.fn__Suffix (
       @Val  int                   ) returns char(2)
    as begin
          return (
             case cast(cast(reverse(cast(@Val as varchar(5))) as char(1)) as int)
                when 1 then case when @Val = 1 then 'st' else 'th' end
                when 2 then 'nd'
                when 3 then 'rd'
                else 'th'
             end
          )
       end
    go
    if object_id('dbo.fn__RunDays') is not null
       drop function dbo.fn__RunDays
    go
    create function dbo.fn__RunDays (
       @fType   int
      ,@fInterval  int              ) returns varchar(max)
    as begin
          declare @runDays varchar(max)
          if @fType is null
             return 'N/A'
          if @fType not in (8,16,32,64,128) begin
             set @runDays = case @fType when 1 then 'Unused' when 4 then 'Daily' else 'UNKNOWN' end
             return @runDays
          end else if @fType in (64,128) begin
             if @fType = 64 --SQLAgent Starts
                set @runDays = 'when SQLAgent Starts'
             else --@fType = 128 - SQLServer Starts and CPU is idle
                set @runDays = 'when CPU(s) become idle'
             return @runDays
          end
          set @runDays = ''
          if @fType = 8 begin --Weekly
             -- I am sure there is a more elegant way of doing it here ;)
             if @fInterval &  1 =  1 set @runDays = @runDays + case when @runDays = '' then '' else ',' end + 'Sunday'
             if @fInterval &  2 =  2 set @runDays = @runDays + case when @runDays = '' then '' else ',' end + 'Monday'
             if @fInterval &  4 =  4 set @runDays = @runDays + case when @runDays = '' then '' else ',' end + 'Tuesday'
             if @fInterval &  8 =  8 set @runDays = @runDays + case when @runDays = '' then '' else ',' end + 'Wednesday'
             if @fInterval & 16 = 16 set @runDays = @runDays + case when @runDays = '' then '' else ',' end + 'Thursday'
             if @fInterval & 32 = 32 set @runDays = @runDays + case when @runDays = '' then '' else ',' end + 'Friday'
             if @fInterval & 64 = 64 set @runDays = @runDays + case when @runDays = '' then '' else ',' end + 'Saturday'
          end else if @fType = 16 --Monthly
             set @runDays = dbo.fn__Suffix(@fInterval) + ' day of the month'
          else if @fType = 32 begin --Monthly relative
             if      @fInterval = 1  set @runDays = 'Sunday'
             else if @fInterval = 2  set @runDays = 'Monday'
             else if @fInterval = 3  set @runDays = 'Tuesday'
             else if @fInterval = 4  set @runDays = 'Wednesday'
             else if @fInterval = 5  set @runDays = 'Thursday'
             else if @fInterval = 6  set @runDays = 'Friday'
             else if @fInterval = 7  set @runDays = 'Saturday'
             else if @fInterval = 8  set @runDays = 'Day'
             else if @fInterval = 9  set @runDays = 'Week Day'
             else if @fInterval = 10 set @runDays = 'Weekend Day'
          end
          return @runDays
       end
    go
    select
       JobName = j.name
      ,Category = c.name
      ,JobDescription = j.description
      ,NumberOfSteps = (select count(*) from msdb.dbo.sysjobsteps js where j.job_id = js.job_id)
      ,DateCreated = j.date_created
      ,JobEnabled = case j.enabled when 1 then 'Yes' when 0 then 'No' else 'UNKNOWN' end
      ,ScheduleName = isnull(s.name, 'Not Scheduled')
      ,ScheduleEnabled =
       case
          when s.enabled is null then 'Not Scheduled'
          when s.enabled = 1 then 'Yes'
          when s.enabled = 0 then 'No'
          else 'UNKNOWN'
       end
      ,Frequency =
       case
          when s.freq_type = 1   then 'One time only'
          when s.freq_type = 4   then 'Daily'
          when s.freq_type = 8   then 'Weekly'
          when s.freq_type = 16  then 'Monthly'
          when s.freq_type = 32  then 'Monthly, relative to interval'
          when s.freq_type = 64  then 'When SQLAgent starts'
          when s.freq_type = 128 then 'When SQLServer starts'
          else 'N/A'
       end
      ,RelativeFrequency =
       case
          when s.freq_type = 4 then 'every ' + cast(s.freq_interval as varchar(5)) + ' day(s)'
          when s.freq_type = 8 then 'every ' + cast(s.freq_recurrence_factor as varchar(5)) + ' week(s)'
          when s.freq_type = 32 then
             case
                when s.freq_relative_interval = 0  then 'Unused'
                when s.freq_relative_interval = 1  then 'First'
                when s.freq_relative_interval = 2  then 'Second'
                when s.freq_relative_interval = 4  then 'Third'
                when s.freq_relative_interval = 8  then 'Fourth'
                when s.freq_relative_interval = 16 then 'Last'
             end
          when s.freq_type = 16 then 'on the day of RunDays'
          else 'N/A'
       end
      ,RunDays =
       case
          when s.freq_type = 16 then cast(s.freq_interval as varchar(5)) +
             msdb.dbo.fn__RunDays(s.freq_type, s.freq_interval) + ' of the ' +
             cast(s.freq_recurrence_factor as varchar(5)) + msdb.dbo.fn__Suffix(s.freq_recurrence_factor) + ' month'
          when s.freq_type = 32 then msdb.dbo.fn__RunDays(s.freq_type, s.freq_interval) + ' of the ' +
             cast(s.freq_recurrence_factor as varchar(5)) + msdb.dbo.fn__Suffix(s.freq_recurrence_factor) + ' month'
          else 'N/A'
       end
      ,SubFrequency =
       case isnull(s.freq_subday_type, 0)
          when 0 then 'N/A'
          when 1 then 'At specified time'
          when 2 then 'every ' + cast(s.freq_subday_interval as varchar(5)) + ' second(s)'
          when 4 then 'every ' + cast(s.freq_subday_interval as varchar(5)) + ' minute(s)'
          when 8 then 'every ' + cast(s.freq_subday_interval as varchar(5)) + ' hour(s)'
       end
      ,JobStartDate =
       case isnull(s.active_start_date,0)
          when 0 then 'Never'
          else cast(cast(cast(nullif(s.active_start_date,0) as varchar(8)) as date) as varchar(10))
       end
      ,JobStartTime = stuff(stuff(right('000000'+cast(isnull(s.active_start_time, 0) as varchar(6)),6),3,0,':'),6,0,':')
      ,JobEndDate =
       case isnull(s.active_end_date,0)
          when 0 then 'Never'
          else cast(cast(cast(nullif(s.active_end_date,0) as varchar(8)) as date) as varchar(10))
       end
      ,JobEndTime = stuff(stuff(right('000000'+cast(isnull(s.active_end_time, 0) as varchar(6)),6),3,0,':'),6,0,':')
      ,JobNextRunDate =
       case isnull(sj.next_run_date,0)
          when 0 then 'Never'
          else cast(cast(cast(nullif(sj.next_run_date,0) as varchar(8)) as date) as varchar(10))
       end
      ,JobNextRunTime = stuff(stuff(right('000000'+cast(isnull(sj.next_run_time, 0) as varchar(6)),6),3,0,':'),6,0,':')
       from msdb.dbo.sysjobs j
       inner join msdb.dbo.syscategories c
          on j.category_id = c.category_id
       left outer join msdb.dbo.sysjobschedules sj
          on j.job_id = sj.job_id
       left outer join msdb.dbo.sysschedules s
          on sj.schedule_id = s.schedule_id
       order by j.job_id, s.schedule_id
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2008
    Posts
    10

    Smile Thank you

    Thanks for the reply I will try it out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •