use admin
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects 
           where id = object_id(N'[dbo].[IndexMaintenanceForDB]') 
           and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[IndexMaintenanceForDB]
GO

CREATE procedure dbo.IndexMaintenanceForDB
	@DatabaseName varchar(128)
	, @NotifyOnFail varchar(100) = '<valid SMTP recipient list>' 
	, @LogUsedThresholdKB int = 4000000 -- 4GB
as
/******************************************************************************
* dbo.indexMaintenanceForDB.PRC
* Bill Wunder	
* Date: 5/24/2002
*
* Outline: Identify which indexes need to be maintained and which maintenance 
*   operation to use. If scan density is less than 90% or logical scan 
*   fragmentation is more than 10% maintain the index. If index has over 10000
*   pages use DBCC INDEXDEFRAG to maintain otherwise use DBCC DBREINDEX. If 
*   table has a clustered index that will be maintained by a DBCC DBREINDEX do
*   not maintain other indexes because they will be rebuilt when the clustered
*   index is reindexed. Pause any active DBCC INDEXDEFRAG if a backup or 
*   restore is running or if log used size has exceeded @LogUsedThresholdKB.
*   Keep all fragmentation study history for trend analysis.
*
* Statistic                   Description 
* Count Pages                 Number of pages in the table or index. 
* Extents Scanned             Number of extents in the table or index. 
* Extent Switches             Number of times the DBCC statement moved from 
*                             one extent to another while it traversed the 
*                             pages of the table or index. 
* Scan Density                [Best Count: Actual Count] Best count is the 
*                             ideal number of extent changes if everything is
*                             contiguously linked. Actual count is the actual
*                             number of extent changes. The number in scan
*                             density is 100 if everything is contiguous; if 
*                             it is less than 100, some fragmentation exists.
*                             Scan density is a percentage. 
* Logical Scan Fragmentation 	Percentage of out-of-order pages returned from 
*                             scanning the leaf pages of an index. This number 
*                             is not relevant to heaps and text indexes. An 
*                             out of order page is one for which the next page
*                             indicated in an IAM is a different page than the 
*                             page pointed to by the next page pointer in the 
*                             leaf page. 
*!---->following not included in DBCC SHOWCONTIG() WITH FAST mode<-----!
* CountRows                   Number of rows in the table or index.
* Avg. Pages per Extent       Number of pages per extent in the page chain. 
* Extent Scan Fragmentation   Percentage of out-of-order extents in scanning 
*                             the leaf pages of an index. This number is not 
*                             relevant to heaps. An out-of-order extent is one 
*                             for which the extent containing the current page 
*                             for an index is not physically the next extent 
*                             after the extent containing the previous page 
*                             for an index. 
* Avg. Bytes free per page    Average number of free bytes on the pages 
*                             scanned. The higher the number, the less full 
*                             the pages are. Lower numbers are better. This 
*                             number is also affected by row size; a large 
*                             row size can result in a higher number. 
* Avg. Page density (full)    Average page density (as a percentage). This 
*                             value takes into account row size, so it is a 
*                             more accurate indication of how full your pages
*                             are. The higher the percentage, the better. 
*
* status 
*   0 - under consideration
*   1 - defrag
*   2 - defrag in progress
*   3 - defrag complete
*   4 - reindex
*   5 - reindex in progress
*   6 - reindex complete	
*   7 - After defrag showcontig
*   8 - manually populate showcontig
*   9 - eliminated from consideration
*   10 - 19 - historical 
*
* usage:		
    EXECUTE admin.dbo.indexMaintenanceForDB 'admin'
* Notes:		
*			
* Modifications   
* bw 04-25-03 Change showcontig to fast mode and make defrags interruptable 
*             based on log space                        
* bw 04-27-03 Speed up the showcontig and add a duration of showcontig column
* bw 12-22-03 Add logic to reindex if LogicalFrag > 10 in addition to case 
*             where ScanDensity < 90
* bw 03-13-04 Add showcontig to fraglist immediately AFTER maint to see how it 
*             went. Run indexdefrags through called proc and manage log growth
*             try not to run INDEXDEFRAG during backup operation on the server
*******************************************************************************/
declare @SQLStr nvarchar(4000)
 , @ObjectOwner varchar(128)
 , @ObjectName varchar(128)
 , @IndexName varchar(128)
 , @status int
 , @DBCCStartDt datetime
 , @JobCategory varchar(128)
 , @JobName varchar(1024)   
 , @JobDesc varchar(128)
 , @JobCmd varchar(2048)
 , @JobCmd2 varchar(2048)
 , @JobOwner sysname
 , @OutputFile varchar(1024)
 , @rc int
 , @ec int
 , @ErrDesc varchar(1024)
 , @Err varchar(300)

-- Verify requested db does exist, if not then notify and keep moving.
if db_id(@DatabaseName) is null
 begin
  select @Err = 'Database (' + @DatabaseName 
              + ') does not exist.  Resuming processing on next DB.'
  exec sysmon.dbo.safe_sendmail @recipients = @NotifyOnFail 
  return
 end

-- create the temp table once for reuse
CREATE TABLE #fraglist 
 ( ObjectName varchar (255)
 , ObjectId INT
 , IndexName varchar (255)
 , IndexId INT
 , Lvl INT
 , CountPages INT
 , CountRows INT
 , MinRecSize INT
 , MaxRecSize INT
 , AvgRecSize INT
 , ForRecCount INT
 , Extents INT
 , ExtentSwitches INT
 , AvgFreeBytes INT
 , AvgPageDensity INT
 , ScanDensity DECIMAL
 , BestCount INT
 , ActualCount INT
 , LogicalFrag DECIMAL
 , ExtentFrag DECIMAL)

-- Table structure will work with or with out WITH FAST showcontig option
if object_id('admin.dbo.fraglist','U') is null
 CREATE TABLE admin.dbo.fraglist 
  ( DatabaseName varchar (128) NOT NULL
  , ObjectOwner varchar(128) NOT NULL		
  , ObjectName varchar (128) NOT NULL
  , ObjectId int NULL
  , IndexName varchar(128) NOT NULL
  , IndexId int NULL
  , Lvl int NULL
  , CountPages int NULL
  , CountRows int NULL
  , MinRecSize int NULL
  , MaxRecSize int NULL
  , AvgRecSize int NULL
  , ForRecCount int NULL
  , Extents int NULL
  , ExtentSwitches int NULL
  , AvgFreeBytes int NULL
  , AvgPageDensity int NULL
  , ScanDensity decimal NULL
  , BestCount int NULL
  , ActualCount int NULL
  , LogicalFrag decimal NULL
  , ExtentFrag decimal NULL
  , RecCreatedDt datetime NOT NULL
    constraint dft__fraglist__RecCreatedDt DEFAULT (getdate())
  , Status tinyint NULL
    constraint dft__fraglist__Status DEFAULT (0)
  , SecondsToMaintain int	NULL
  , SecondsToShowContig int NULL
  , constraint 
    pkc_fraglist__DbName__ObjOwner__ObjName__IdxName__RecCreatedDt 
    primary key clustered 
     ( DatabaseName
     , ObjectOwner
     , ObjectName
     , IndexName
     , RecCreatedDt) on [PRIMARY]
    ) on [PRIMARY]

-- build a utiltiy proc to handle showcontig population of fraglist
set @SQLStr = 
'Create procedure dbo.GetShowContigForIndex
   @DatabaseName varchar(128)
 , @ObjectOwner varchar(128) = ''dbo''
 , @ObjectName varchar(128) = null
 , @IndexName varchar(128) = null
 , @PlanMaintenance tinyint = 8
as
/******************************************
* see IndexMaintenanceForDB
*
* status 
*  0 - under consideration
*  1 - defrag
*  2 - defrag in progress
*  3 - defrag complete
*  4 - reindex
*  5 - reindex in progress
*  6 - reindex complete	
*  7 - After defrag showcontig       (no-op)
*  8 - manually populate showcontig  (no-op)
*  9 - eliminated from consideration (no-op)
*  10 - 19 - historical 
*******************************************/
declare @SQLStr nvarchar(4000)
 , @ShowContigStartDt datetime

-- table should alrady exist unless call is one off
if object_id(''tempdb.dbo.#fraglist'',''U'') is null
 CREATE TABLE #fraglist 
  ( ObjectName varchar (255)
  , ObjectId INT
  , IndexName varchar (255)
  , IndexId INT
  , Lvl INT
  , CountPages INT
  , CountRows INT
  , MinRecSize INT
  , MaxRecSize INT
  , AvgRecSize INT
  , ForRecCount INT
  , Extents INT
  , ExtentSwitches INT
  , AvgFreeBytes INT
  , AvgPageDensity INT
  , ScanDensity DECIMAL
  , BestCount INT
  , ActualCount INT
  , LogicalFrag DECIMAL
  , ExtentFrag DECIMAL)

set @SQLStr = ''use '' 
            + @DatabaseName 
            + '' DBCC SHOWCONTIG(['' 
            + @ObjectName 
            + ''],['' 
            + @IndexName 
            + '']) WITH TABLERESULTS, FAST''

set @ShowContigStartDt = getdate()

insert #fraglist 
exec sp_executesql @SQLStr

insert admin.dbo.fraglist 
 ( DatabaseName
 , ObjectOwner
 , ObjectName
 , ObjectId
 , IndexName
 , IndexId
 , Lvl
 , CountPages
 , CountRows
 , MinRecSize
 , MaxRecSize
 , AvgRecSize
 , ForRecCount
 , Extents
 , ExtentSwitches
 , AvgFreeBytes
 , AvgPageDensity
 , ScanDensity
 , BestCount
 , ActualCount
 , LogicalFrag
 , ExtentFrag
 , Status
 , SecondsToShowContig)
select @DatabaseName
 , @ObjectOwner
 , ObjectName
 , ObjectId
 , IndexName
 , IndexId
 , Lvl
 , CountPages
 , CountRows
 , MinRecSize
 , MaxRecSize
 , AvgRecSize
 , ForRecCount
 , Extents
 , ExtentSwitches
 , AvgFreeBytes
 , AvgPageDensity
 , ScanDensity
 , BestCount
 , ActualCount
 , LogicalFrag
 , ExtentFrag
 , Status = 
   case	
    when @PlanMaintenance = 0 then
     case
      when (ObjectName = ''fraglist''
            or ScanDensity > 90
            or LogicalFrag < 10
            or (Indexid > 1 
                and exists(select 1 
                           from admin.dbo.fraglist 
                           where DatabaseName = @DatabaseName
                           and ObjectOwner = @ObjectOwner
                           and ObjectName = @ObjectName
                           and IndexId = 1 
                           and status = 4))) -- reindex clustered does all
       then 9  -- noop
      when CountPages < 10000
       then 4  -- reindex 
       else 1  -- defrag
       end
   else @PlanMaintenance
   end 
 , datediff(second,@ShowContigStartDt,getdate())
from #fraglist 

delete #fraglist'

if object_id('dbo.GetShowContigForIndex','P') is not null
 begin
  exec @rc = sp_executesql N'drop procedure dbo.GetShowContigForIndex'
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'drop procedure dbo.GetShowContigForIndex failed'
    goto ErrorHandler
   end
 end
exec @rc = sp_executesql @SQLStr 
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'create procedure dbo.GetShowContigForIndex failed'
  goto ErrorHandler
 end

-- add components to prevent log growth for all INDEXDEFRAGs
-- do not DEFRAG if a backup or restore is active
set @SQLStr = 
'CREATE procedure dbo.DefragLargeTables
 @DatabaseName varchar(128)
as
/*
see IndexMaintenanceForDB
*/
declare @SQLStr nvarchar(4000)
 , @ObjectOwner varchar(128) 
 , @ObjectName varchar(255)
 , @IndexName varchar(255)
 , @status int
 , @DBCCStartDt datetime

-- one at a time until someone says stop, 2s before 1s
select top 1 @DatabaseName = Databasename
 , @ObjectOwner = ObjectOwner
 , @ObjectName = ObjectName
 , @IndexName = IndexName
 , @status = status
from admin.dbo.fraglist
where status in (1,2)
and DatabaseName = @DatabaseName
order by status desc

-- mark as current maintenance candidate if is a 1
update admin.dbo.fraglist		
set status = 2
where DatabaseName = @DatabaseName
and ObjectOwner = @ObjectOwner
and ObjectName = @ObjectName
and IndexName = @IndexName
and status = 1

set @SQLStr = ''DBCC INDEXDEFRAG('''''' 
               + @DatabaseName 
               + '''''',''''''
               + @ObjectOwner 
               + ''.''
               + @ObjectName 
               + '''''',''''''
               + @IndexName 
               + '''''') '' +
               + ''UPDATE STATISTICS ['' 
               + @DatabaseName 
               + ''].['' 
               + @ObjectOwner 
               + ''].['' + @ObjectName 
               + ''] ['' 
               + @IndexName 
               + '']''

set @DBCCStartDt = getdate()

exec sp_executesql @SQLStr 

-- mark as maintenance complete
update admin.dbo.fraglist		
set status = 3
 , SecondsToMaintain = Datediff(second,@DBCCStartDt,getdate()) 
where DatabaseName = @DatabaseName
and ObjectOwner = @ObjectOwner
and ObjectName = @ObjectName
and IndexName = @IndexName
and status = 2

-- add a fraglist row to show the table/index state after the maintenance
exec admin.dbo.GetShowContigForIndex 
   @DatabaseName = @DatabaseName
 , @ObjectOwner = @ObjectOwner
 , @ObjectName = @ObjectName
 , @IndexName = @IndexName
 , @PlanMaintenance = 7'

set @JobCategory = 'Database Maintenance'
set @JobName = 'w Defrag large ' + @DatabaseName + ' tables'
set @JobDesc = 'defrag indexes - stopped if log space reaches threshold size.'  
set @JobCmd = 'EXEC admin.dbo.DefragLargeTables ' 
            + char(39) + @DatabaseName + char(39)
set @JobCmd2 = 'exec admin.dbo.log_job_error ' 
             + char(39) + @NotifyOnFail + char(39) 
set @JobOwner = 'sa'
set @OutputFile = 'F:\MSSQL\Log\' + @JobName + '.out'
		
if object_id('dbo.DefragLargeTables','P') is not null
 begin
  exec @rc = sp_executesql N'drop procedure dbo.DefragLargeTables'
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'drop procedure dbo.DefragLargeTables failed'
    goto ErrorHandler
   end
 end
exec @rc = sp_executesql @SQLStr 
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'create procedure dbo.DefragLargeTables failed'
  goto ErrorHandler
 end

if exists (select 1 from msdb.dbo.sysJobs where name = @JobName)  
 begin
  exec @rc = msdb.dbo.sp_delete_job @job_name = @JobName
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'sp_delete_job failed'
    goto ErrorHandler
   end
 end

if not exists (select 1 
               from msdb.dbo.syscategories 
               where name = @JobCategory)  
 begin
  exec @rc = msdb.dbo.sp_add_category  
     @class = 'Job'
   , @type = 'local'
   , @name = @JobCategory
 set @ec = @@error
 if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'sp_add_category failed'
    goto ErrorHandler
   end
 end
	
exec @rc = msdb.dbo.sp_add_job 
   @job_name = @JobName
 , @owner_login_name = @JobOwner
 , @description = @JobDesc
 , @category_name = @JobCategory
 , @enabled = 1
 , @notify_level_email = 0
 , @notify_level_page = 0
 , @notify_level_netsend = 0
 , @notify_level_eventlog = 2
 , @delete_level= 0
set @ec = @@error

if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_add_job failed'
  goto ErrorHandler
 end

exec @rc = msdb.dbo.sp_add_jobstep 
   @job_name = @JobName
 , @step_id = 1
 , @step_name = 'Defrag large tables'
 , @command = @JobCmd
 , @database_name = 'admin'
 , @database_user_name = ''
 , @subsystem = 'TSQL'
 , @cmdexec_success_code = 0
 , @flags = 4
 , @retry_attempts = 0
 , @retry_interval = 1
 , @output_file_name = @OutputFile
 , @on_success_step_id = 0
 , @on_success_action = 1
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_add_jobstep 1 failed'
  goto ErrorHandler
 end

exec @rc = msdb.dbo.sp_add_jobstep 
   @job_name = @JobName
 , @step_id = 2
 , @step_name = 'failure notification'
 , @command = @JobCmd2
 , @database_name = 'admin'
 , @database_user_name = ''
 , @subsystem = 'TSQL'
 , @cmdexec_success_code = 0
 , @flags = 2
 , @retry_attempts = 0
 , @retry_interval = 1
 , @output_file_name = @OutputFile
 , @on_success_step_id = 0
 , @on_success_action = 2
 , @on_fail_step_id = 0
 , @on_fail_action = 2
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_add_jobstep 2 failed'
  goto ErrorHandler
 end

-- add step 1 notification link after creating step 2 to avoid warning message
exec @rc = msdb.dbo.sp_update_jobstep 
   @job_name = @JobName
 , @step_id = 1
 , @on_fail_step_id = 2
 , @on_fail_action = 4
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_update_jobstep 1 failed'
  goto ErrorHandler
 end

exec @rc = msdb.dbo.sp_add_jobserver 
   @job_name = @JobName
 , @server_name = '(local)' 
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_add_jobserver failed'
  goto ErrorHandler
 end

exec @rc = msdb.dbo.sp_update_job 
   @job_name = @JobName
 , @start_step_id = 1 
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_update_job failed'
  goto ErrorHandler
 end
-- end -- add components for full recovery db

set nocount on

-- delete archive over 90 days
delete admin.dbo.fraglist
where DatabaseName = @DatabaseName
and RecCreatedDt < getdate() - 90

-- archive previous run
update admin.dbo.fraglist
set status = status + 10
where DatabaseName = @DatabaseName
and status < 10

-- fragmentation study (find 1s and 4s)
-- maintain sequential index processing order
select @SQLStr = 'use ' + @DatabaseName + '
declare @ObjectId int
 , @IndexId int
 , @DatabaseName varchar(128)
 , @ObjectOwner varchar(128)
 , @ObjectName varchar(128)
	, @IndexName varchar(128)

set nocount on

select @ObjectId = min(id) 
from dbo.sysobjects 
where type = ''U''

while @ObjectId is not null
 begin
  Select @IndexId = min(indid) 
  from dbo.sysindexes 
  where id = @ObjectId 
  and indid > 0 
  and indid < 255

  while @IndexId is not null
   begin
    select @DatabaseName = db_name()
     , @ObjectOwner = user_name(o.uid)
     , @ObjectName = o.name
     , @IndexName = i.name
    from dbo.sysobjects o
    join dbo.sysindexes i
    on o.id = i.id
    where o.id = @ObjectId
    and i.indid = @IndexId

    -- determine if the index ought to be indexed
    exec admin.dbo.GetShowContigForIndex 
       @DatabaseName = @DatabaseName
     , @ObjectOwner = @ObjectOwner
     , @ObjectName = @ObjectName
     , @IndexName = @IndexName
     , @PlanMaintenance = 0

    Select @IndexId = min(indid) 
    from dbo.sysindexes 
    where id = @ObjectId 
    and indid > @IndexId 
    and indid < 255

   end
  select @ObjectId = min(id) 
  from dbo.sysobjects 
  where type = ''U'' 
  and id > @ObjectId
 end'
exec @rc = sp_executesql @sqlStr
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'DBCC showcontig failed processing'
  goto ErrorHandler
 end

-- perform all identified required maintenance
-- first reindex all the small tables
declare indexesToMaintainList insensitive cursor 
for
select ObjectOwner, ObjectName, IndexName, status
from admin.dbo.fraglist
where DatabaseName = @DatabaseName
and status = 4

open indexesToMaintainList

fetch next 
from indexesToMaintainList 
into @ObjectOwner, @ObjectName, @IndexName, @status

while @@fetch_status = 0
 begin
  -- mark as current maintenance candidate
  update admin.dbo.fraglist		
  set status = 5
  where DatabaseName = @DatabaseName
  and ObjectOwner = @ObjectOwner
  and ObjectName = @ObjectName
  and IndexName = @IndexName
  and status = 4

  set @SQLStr = 'DBCC DBREINDEX(''' 
              + @DatabaseName 
              + '.' 
              + @ObjectOwner 
              + '.' 
              + @ObjectName 
              + ''',''' 
              + @IndexName 
              + ''')'					
  set @DBCCStartDt = getdate()
  exec @rc = sp_executesql @sqlstr 
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Defragmentation operation "' + @SQLStr + '" failed'
    goto ErrorHandler
   end

  -- mark as maintenance complete
  update admin.dbo.fraglist		
  set status = 6
   , SecondsToMaintain = Datediff(second,@DBCCStartDt, getdate())
  where DatabaseName = @DatabaseName
  and ObjectOwner = @ObjectOwner
  and ObjectName = @ObjectName
  and IndexName = @IndexName
  and status = 5
		
  exec admin.dbo.GetShowContigForIndex 
     @DatabaseName = @DatabaseName
   , @ObjectOwner = @ObjectOwner
   , @ObjectName = @ObjectName
   , @IndexName = @IndexName
   , @PlanMaintenance = 7
 	
  fetch next 
  from indexesToMaintainList 
  into @ObjectOwner, @ObjectName, @IndexName, @status
 end
close indexesToMaintainList
deallocate indexesToMaintainList

-- now INDEXDEFRAG all the larger tables
while exists (select 1 from admin.dbo.fraglist
              where DatabaseName = @DatabaseName
              and status in (1,2))
 begin
  if (select cntr_value from master..sysperfinfo
      where instance_name = @DatabaseName
      and counter_name = 'Log File(s) Used Size (KB)') > @LogUsedThresholdKB
   begin
    if  exists (select 1 
                from master.dbo.sysprocesses p with(nolock)
                join msdb.dbo.sysjobs j with(nolock)
                on substring(p.program_name
                            , charindex('0x', p.program_name) + 18
                            , 16) 
                 = substring(replace(j.job_id, '-',''),17,16) 
                where j.name = @JobName)
    or exists (select 1 
               from master.dbo.sysprocesses with(nolock)
               where (cmd like 'BACKUP%'
                      or cmd like 'RESTORE%')
               and dbid = db_id(@DatabaseName))
     begin
      exec admin.dbo.IsAgentJobRunnable @IsRunnable OUTPUT, @JobName, @JobOwner				
      if  @IsRunnable = 0
       begin
        -- stop the job
	exec @rc = msdb.dbo.sp_stop_job @JobName
        set @ec = @@error
        if @rc <> 0 or @ec <> 0
         begin
          set @Err = 'sp_stop_job ' + @JobName + ' failed'
          goto ErrorHandler
         end
       end
     end     
     -- Set all job steps to append to log file
     exec @rc = msdb.dbo.sp_update_jobstep 
        @job_name = @JobName
      , @step_id = 1
      , @flags = 2
     set @ec = @@error
     if @rc <> 0 or @ec <> 0
      begin
       set @Err = 'sp_update_jobstep 1 to append log file failed'
       goto ErrorHandler
      end
    --give log backup a chance to catch up
    waitfor delay '00:05:00'
   end
  else -- enough log space to keep going
   begin
    -- start the job if it's not running 
    if not exists (select 1 from master.dbo.sysprocesses p
                   join msdb.dbo.sysjobs j
                   on substring(p.program_name
                               , charindex('0x', p.program_name) + 18
                               , 16) 
                    = substring(replace(j.job_id, '-',''),17,16) 
                   where j.name = @JobName)
     begin
      exec admin.dbo.IsAgentJobRunnable @IsRunnable OUTPUT, @JobName, @JobOwner				
      if  @IsRunnable = 1
       begin
        exec @rc = msdb.dbo.sp_start_job @JobName
        set @ec = @@error
        if @rc <> 0 or @ec <> 0
         begin
          set @Err = 'sp_start_job ' + @JobName + ' failed'
         	goto ErrorHandler
         end
       end
     end
    -- wait a minute before checking again	
    waitfor delay '00:01:00'
   end
 end

-- cleanup
if exists (select 1 from msdb.dbo.sysJobs where name = @JobName)  
 begin
  exec @rc = msdb.dbo.sp_delete_job @job_name = @JobName
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'sp_delete_job during cleanup failed'
    goto ErrorHandler
   end
 end

return
ErrorHandler:

set @ErrDesc = 'admin.dbo.IndexMaintenanceForDB failed ' 
             + '| Return Code %d ' 
             + '| Error Code %d ' 
             + '| @DatabaseName = %s ' 
             + '| @ObjectOwner = %s ' 
             + '| @ObjectName = %s ' 
             + '| @IndexName = %s ' 
             + '| Error: %s' 
raiserror 
 ( @ErrDesc
 , 16
 , 1
 , @rc
 , @ec
 , @DatabaseName
 , @ObjectOwner
 , @ObjectName
 , @IndexName
 , @Err)
return -1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO