Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

listing Maintenance plan jobs

Status
Not open for further replies.
jpotucek

jpotucek

Technical User
Jan 26, 2005
144
US
This SQL will list Maintenance Plan job but it only works on a SQL Serve 2000 Instance. How do I modify this so that it will work against a SQL Server 2005 Instance????

Code: 
USE MSDB
GO

SET NOCOUNT ON

create table #temp
([Job Name] varchar(128),
[Job Enabled] varchar(3),
[Plan Name] varchar(128),
[Job Type] varchar(50),
[Databases] varchar(1000),
[Schedule Name] varchar(128),
[Schedule Enabled] varchar(3),
[Schedule] varchar(20),
[Interval] varchar(200),
[Start Date] char(10),
[End Date] char(10)
)

declare @job_name varchar(128), @JobEnabled varchar(3),
 @plan_name varchar(128), @ScheduleName varchar(128), @ScheduleEnabled varchar(3),
@job_type varchar(50), @plan_id uniqueidentifier,
 @dbString varchar(1000), @freq_interval int, @frequency varchar(20),
@Interval varchar(200), @freq_type int, @freq_recurrence_factor int,
@freq_relative_interval int, @freq_subday_type int, @freq_subday_interval int,
@active_start_time int, @active_end_time int,
@xActive_start_time varchar(8), @xActive_end_time varchar(8),
@startdate char(10), @enddate char(10)

declare maintCursor cursor for
select sj.name as job_Name,
  case when sj.enabled = 1 then 'Yes'
  when sj.enabled = 0 then 'No'
  end
as JobEnabled,
sd.plan_id, sm.plan_name, sch.name, 
  case when sch.enabled = 1 then 'Yes'
  when sch.enabled = 0 then 'No'
  end
as ScheduleEnabled,
sch.freq_interval,
  case when js.command like '%-BkUpDB%' then 'Database backup job'
  when js.command like '%-BkUpLog%' then 'Transaction log backup job'
  when js.command like '%-CkDB%' then 'Integrity checks job'
  when js.command like '%-RebldIdx%' then 'Optimizations job'
  end
as [Job_Type],
  case when sch.freq_type = 16 or sch.freq_type = 32 then 'Monthly job'
  when sch.freq_type = 8 then 'Weekly Job'
  when sch.freq_type = 4 then 'Daily Job'
  end
as [Schedule],
sch.freq_type, sch.freq_recurrence_factor, sch.freq_relative_interval,
sch.freq_subday_type, sch.freq_subday_interval,
sch.active_start_time, sch.active_end_time,
sch.active_start_date, sch.active_end_date
from sysjobs sj inner join sysdbmaintplan_jobs sd
  on sj.job_id = sd.job_id inner join sysdbmaintplans sm
    on sd.plan_id = sm.plan_id inner join sysjobsteps js
      on sj.job_id = js.job_id inner join sysjobschedules sch
        on sch.job_id = sj.job_id
order by sj.name


open maintCursor

fetch next from maintCursor into @job_Name, @JobEnabled, @plan_id, @plan_name,
@ScheduleName, @ScheduleEnabled, @freq_interval, @job_type, @frequency,
@freq_type, @freq_recurrence_factor, @freq_relative_interval, @freq_subday_type,
@freq_subday_interval, @active_start_time, @active_end_time, @StartDate, @EndDate

while @@fetch_status = 0

begin

select @xActive_start_time = replicate('0', 6 - len(@active_start_time)) + cast(@active_start_time as varchar)
select @xActive_end_time = replicate('0', 6 - len(@active_end_time)) + cast(@active_end_time as varchar)
select @xActive_start_time = substring(@xActive_start_time, 1, 2) + ':' + substring(@xActive_start_time, len(@xActive_start_time) - 3, 2) + ':' + right (@xActive_start_time, 2)
select @xActive_end_time = substring(@xActive_end_time, 1, 2) + ':' + substring(@xActive_end_time, len(@xActive_end_time) - 3, 2) + ':' + right (@xActive_end_time, 2)

select @dbString = ''
select @Interval = ''

select @dbString = @dbString + database_name + ', ' from sysdbmaintplan_databases
where plan_id = @plan_id

if @freq_type = 4 
  begin
  select @Interval = @Interval + 'Every ' + cast(@freq_interval as varchar) + ' day(s).'
  if @freq_subday_type = 1 
    begin
    select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
    end
  else
    begin
    select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
      case cast(@freq_subday_type as varchar)
      when '4' then ' minute(s)'
      when '8' then ' hour(s)'
    end
    select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
   end
  end

if @freq_type = 8 
  begin
  select @Interval = @Interval + 'Every ' + cast(@freq_recurrence_factor as varchar) + ' week(s) on'
    if @freq_interval & 1 = 1 select @Interval = @Interval + ' SUN'
    if @freq_interval & 2 = 2 select @Interval = @Interval + ' MON'
    if @freq_interval & 4 = 4 select @Interval = @Interval + ' TUE'
    if @freq_interval & 8 = 8 select @Interval = @Interval + ' WED'
    if @freq_interval & 16 = 16 select @Interval = @Interval + ' THU'
    if @freq_interval & 32 = 32 select @Interval = @Interval + ' FRI'
    if @freq_interval & 64 = 64 select @Interval = @Interval + ' SAT'
  select @Interval = @Interval + '.'
  if @freq_subday_type = 1 
    begin
    select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
    end
  else
    begin
    select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
      case cast(@freq_subday_type as varchar)
      when '4' then ' minute(s)'
      when '8' then ' hour(s)'
      end
    select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
    end
  end

if @freq_type = 16
  begin
  select @Interval = 'Day ' + cast(@freq_interval as varchar) + ', every ' + cast(@freq_recurrence_factor as varchar) + ' month(s).'
    if @freq_subday_type = 1 
      begin
      select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
      end
    else
      begin
      select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
        case cast(@freq_subday_type as varchar)
        when '4' then ' minute(s)'
        when '8' then ' hour(s)'
        end
      select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
    end
  end 

if @freq_type = 32
  begin
  select @Interval = 'The ' +
    case cast(@freq_relative_interval as varchar)
    when '1' then 'first '
    when '2' then 'second '
    when '4' then 'third '
    when '8' then 'fourth '
    when '16' then 'last '
    end
  +
    case cast(@freq_interval as varchar)
    when '1' then 'Sunday' 
    when '2' then 'Monday'
    when '3' then 'Tuesday'
    when '4' then 'Wednesday'
    when '5' then 'Thursday'
    when '6' then 'Friday'
    when '7' then 'Saturday'
    when '8' then 'day'
    when '9' then 'weekday'
    when '10' then 'weekend day'
    end
  +
  ', every ' + cast(@freq_recurrence_factor as varchar) + ' month(s).'
    if @freq_subday_type = 1 
      begin
      select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
      end
    else
      begin
      select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
        case cast(@freq_subday_type as varchar)
        when '4' then ' minute(s)'
        when '8' then ' hour(s)'
        end
      select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
    end
  end 

Select @StartDate = substring(@StartDate, 5, 2) + '/' + substring(@StartDate, 7,2) + '/' + substring(@StartDate, 1, 4)
Select @EndDate = substring(@EndDate, 5, 2) + '/' + substring(@EndDate, 7,2) + '/' + substring(@EndDate, 1, 4)

  if right(@EndDate, 4) = '9999' 
  Select @EndDate = 'None'


insert into #temp
([Job Name], [Job Enabled], [Plan Name], [Job Type], [Databases], [Interval], [Schedule Name], [Schedule Enabled], [schedule], [Start Date], [End Date])
values
(@job_name, @JobEnabled, @plan_name, @job_type, @dbString, @Interval, @ScheduleName, @ScheduleEnabled, @frequency, @StartDate, @EndDate)

fetch next from maintCursor into @job_Name, @JobEnabled, @plan_id, @plan_name,
@ScheduleName, @ScheduleEnabled, @freq_interval, @job_type, @frequency,
@freq_type, @freq_recurrence_factor, @freq_relative_interval, @freq_subday_type,
@freq_subday_interval, @active_start_time, @active_end_time, @StartDate, @EndDate
end

close maintCursor
deallocate maintCursor

select [Plan Name], [Job Name], [Job Enabled],  [Job Type], left(Databases, len(databases)-1) as Databases,
[Schedule Name], [Schedule Enabled], Schedule as [Schedule Type], Interval, [Start Date], [End Date]
from #temp
order by [plan name], [Job Type], [Schedule Name]
drop table #temp

 
Sort by date Sort by votes
jpotucek,

Here is ther reason isn't working:
The table sysdbmaintplan_databases is included in Microsoft SQL Server 2005/2008 to preserve existing information for instances upgraded from a previous version of Microsoft SQL Server. SQL Server 2005/2008 does not change the contents of this table.

AL Almeida
CIO
May all those that come behind us, find us faithful
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

lavadan
  • Locked
  • Question
SQL Maintenance Job Schedule
Replies
0
Views
69
lavadan
lavadan
GCL2007
  • Locked
  • Question
Changed database from Full to simple - Maintenance plan failing
Replies
1
Views
84
SQLBill
SQLBill
awaria
  • Locked
  • Question
Provide Utility to Users to run Jobs
Replies
1
Views
81
SQLBill
SQLBill
GCL2007
  • Locked
  • Question
TempDB and database maintenance Plan
Replies
1
Views
71
gmmastros
gmmastros
Luvsql
  • Locked
  • Question
Maintenance on SQL 2012 - Do you have to take databases offline for mainenance 1
Replies
5
Views
95
Luvsql
Luvsql

Part and Inventory Search

Sponsor

Back
Top