Reindex - Reorganize or Rebuild

  • Comments posted to this topic are about the item Reindex - Reorganize or Rebuild

  • ErrorNumberErrorMessage

    1712Online index operations can only be performed in Enterprise edition of SQL Server.

    :exclamation: 🙁

  • Thanks for the script

  • Got and error when running this proc.

    ErrorNumber: 153

    ErrorMessage: Invalid usage of the option online in the ALTER INDEX REBUILD statement.

    I updated my proc to exclude spatial indexes and it succeeded. The REBUILD WITH ONLINE=ON option for alter index does not work with XML indexes, Spatial indexes or large data type columns (image, text, ntext, varchar(max), nvarchar(max), varbinary(max) or xml.

    Online help reference -

  • Add a parameter @online to the stored procedure, and then change the statement building line to:

    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE=' + isnull(@Online, 'ON') + ')';

    Many DBAs have their own version of the index rebuild/reorganize script.

    Some other good params that I have:

    @SpecifiedTableName,-- Specify the name of the table, null = all

    @SpecifiedIndexName,-- Specify the name of the index, null = all

    @FillFactor,-- Specify the fill factor, null = existing fill factor

  • Thanks for the script and the updates.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply