SQL Server - Error Rebuilding Indexes in Online Mode

In transactional Databases it is a must to have an index rebuilding job running frequently. Fragmentation of indexes is the major cause for any database driven system to perform slower or to crash. As modern database driven application's (Specially Web) are operated 24/7, it demands us to do this operation while the indexes are online.

But SQL Server does not support this operation if you have any LOB data type (text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml) column in your indexes. And this is the part of the error message your getting from SQL Server IDE;

"Online index operation cannot be performed for index 'cndx_PrimaryKey_Account' because the index contains column 'Description' 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 indexes if any of the fields in your underling table is in a LOB data type you can not rebuild that index in online mode.

For other indexes if any of the include columns in the index is in a LOB data type you can not rebuild the index in online mode.

In these cases you will have to rebuilt that particular index offline.

Apart from these two common causes following causes are also possible for failing Online Rebuild of an index;

1. XML index.
2. Index on a local temp table.
3. Initial unique clustered index on a view.
4. Disabled clustered indexes.

Please feel free to drop a comment if you have any questions or suggestions.