Thursday, February 16, 2012

Msg 2725 Online index operation cannot be performed

ALTER INDEX ALL ON dbo.Employee REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,ONLINE = ON , STATISTICS_NORECOMPUTE = OFF)

Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'PK_Employee' because the index contains column Comment' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.
 
Sometimes even if you have Enterprise Edition of SQL Server installed, you can not REBUILD certain indexes using ONLINE=ON.

If the index contains columns that is of the following data types then you can only rebuild the index in offline mode.
text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml.

The only way around this is to rebuild the index using the option ONLINE=OFF.

I have a script that will check the fragmentation for indexes and later run the REBUILDS using ONLINE=ON. If it detects ERROR 2725 then it will rebuild using ONLINE=OFF.

  

3 comments:

  1. Could you please provide that script, plz email @ syed_yp@yahoo.com

    ReplyDelete
  2. can you please send me this script at lakhva@hotmail.com?

    ReplyDelete
    Replies
    1. I know it's a very late reply but check the scripts on https://ola.hallengren.com/
      I personally use it at work too. These scripts are really good.

      Delete