SharePoint 2010 Beta — No Longer Possible to Keep Indexes Online While Rebuilding Them

Due to a change in the SharePoint 2010 AllDocVersions table it is no longer possible to rebuild indexes as part of a SQL 2008 maintenance plan and keep the indexes on-line at the same time.

The reason behind this is that the MetaInfo field is no longer of type Image. It is now of type tCompressedBinary:varbinary(MAX).

If you get the error listed below then make sure you open the Rebuild Index Task in your SQL Maintenance plan and disable the ‘Keep index online while reindexing’ option.

If you don’t then you’ll get the following error:

Executing the query “ALTER INDEX [AllDocVersions_PK] ON [dbo].[AllDocVe…” failed with the following error: “An online operation cannot be performed for index ‘AllDocVersions_PK’ because the index contains column ‘MetaInfo’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

If you use one of the default maintenance plans then this error happens before the Backup step. As a result your databases will not be backed up.