How To: Reindex only indexes that are marked ONLINE = ON (or, only ONLINE = OFF)


You can choose the set of indexes to maintain at a time. One of the filters available is to choose to maintain only the indexes that are set to ONLINE mode. 



Note: The ONLINE=ON option is set in either the Minion.IndexSettingsDB table, or the Minion.IndexSettingsTable table. Alter existing rows, or insert new rows, to set which databases or tables should have the ONLINEopt set to ON.  Any index that is not marked for ONLINE=ON is, by default, an OFFLINE index (whether it is marked for ONLINEopt=OFF or ONLINEopt=NULL).



Note: ONLINE index operations may not be possible for certain editions of SQL Server, and only for indexes that are eligible for ONLINE index operations. If you specify ONLINE when it is not possible, the routine will change it to OFFLINE. (For more information on editions that support Online Reindexing, see the MSDN article "Features Supported by the Editions of SQL Server 2014" at http://msdn.microsoft.com/en-us/library/cc645993.aspx.)


To reindex only indexes marked for ONLINE=ON, run the procedure Minion.IndexMaintMaster with the parameter @IndexOption set to ‘ONLINE’. For example, to reindex only the ONLINE=ON indexes for ALL databases on the instance, use the following call:

EXEC [Minion].[IndexMaintMaster] 
  @IndexOption = 'ONLINE',
  @ReorgMode = 'All',
  @RunPrepped = 0, 
  @PrepOnly = 0,
  @StmtOnly = 0,
  @Include = NULL, 
  @Exclude = NULL, 
  @LogProgress = 1;



To reindex the only the ONLINE=ON indexes for a single database – [YourDatabase] – use the following call:

EXEC [Minion].[IndexMaintMaster] 
  @IndexOption = 'ONLINE',
  @ReorgMode = 'All',
  @RunPrepped = 0, 
  @PrepOnly = 0,
  @StmtOnly = 0,
  @Include = 'YourDatabase', 
  @Exclude = NULL, 
  @LogProgress = 1;



To reindex the only OFFLINE indexes (again, any index which does not have ONLINEopt=ON) for a single database – [YourDatabase] – use the following call:

EXEC [Minion].[IndexMaintMaster] 
  @IndexOption = 'OFFLINE',
  @ReorgMode = 'All',
  @RunPrepped = 0, 
  @PrepOnly = 0,
  @StmtOnly = 0,
  @Include = 'YourDatabase', 
  @Exclude = NULL, 
  @LogProgress = 1;