Reindex - Reorganize or Rebuild

  • Benes Guislandi

    Grasshopper

    Points: 21

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

  • Brett Phipps

    Ten Centuries

    Points: 1373

    ErrorNumber ErrorMessage

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

    :exclamation: 🙁

  • Ken Wymore

    SSCoach

    Points: 16357

    Thanks for the script

  • Ken Wymore

    SSCoach

    Points: 16357

    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

  • pkrudysz

    Right there with Babe

    Points: 742

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script and the updates.

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

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