Can not rebuild online, then reorganize it.

If an index cannot be rebuilt online you can reorganize it.


Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index 'ind1' because the index contains column 'info' 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.

You remember this error message, right? If your index contains any of text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type you can not rebuild the index online. If it a necessity to perform the operation online you can reorganize it. Reorganize runs online and has less load on system. Reorganize may take more time than rebuild but since it runs online it helps a lot. 

If the system is mission critical and has no tolerance to down time, reorganize is a life saving tool.

No comments:

Post a Comment