I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one particular index and I see no reason why I should.
It fails with the following message:
Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index 'I_520CUSTVENDRELIDX' because the index contains column 'MEMO' 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.
However, when I run the query based on a suggestion by this chap, shown below, I get no results:
SELECT * FROM sys.index_columns AS ic INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id AND ((c.system_type_id IN (34,35,99,241)) -- image, text, ntext, xml OR (c.system_type_id IN (167,231,165) -- varchar, nvarchar, varbinary AND max_length = -1)) INNER JOIN sys.indexes as si on si.object_id = ic.object_id AND ic.index_id = si.index_id inner join sys.tables t on t.object_id = ic.object_id where t.name = 'CONTACTPERSON' and si.name = 'I_520CUSTVENDRELIDX'
On top of that, a manual inspection of the index in question shows no text, ntext, image, xml or varchar(MAX), nvarchar(MAX) or varbinary(MAX). Could there be something I'm missing here?
For the record, this is a clustered index.