Post reply

Reindex - Reorganize or Rebuild

  • August 9, 2011 at 11:27 pm

    #241936

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

  • August 10, 2011 at 7:50 am

    #1366558

    ErrorNumberErrorMessage

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

    :exclamation: 🙁

  • August 10, 2011 at 9:16 am

    #1366651

    Thanks for the script

  • August 10, 2011 at 9:53 am

    #1366693

    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 - http://msdn.microsoft.com/en-us/library/ms188388.aspx

  • August 18, 2011 at 6:27 am

    #1370068

    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

  • May 12, 2016 at 7:16 am

    #1878583

    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