Cannot rebuild index, but there's no reason why not?

Question

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.

Answer 1

I skipped the code example, and jumped to what seems to be the real question here

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.

You certainly are missing something, and the answer is in the body of the error message:

For a clustered index, the column could be any column of the table

For Online Index Operations there is a difference between a Clustered and a Non-Clustered index.
both may not contain a BLOB column in order to rebuild online,
but while a Non-Clustered Index "contains" the columns in it's definition (and included columns),
a Clustered Index "contains" the entire table with all of the columns.

in your case there is a column named MEMO which is of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type.
this column is part of the table, and thus part of the Clustered index.

Your options are:

1. Perform the REBUILD operation offline
2. Upgrade to SQL Server 2012
3. Change the column's data type

Guidelines for Performing Online Index Operations

Answer 2

Aaron had missed the spatial data types. Modify it like this.

AND ((c.system_type_id IN (34,35,99,241, 240)) -- image, text, ntext, xml, CLR types
 OR (c.system_type_id IN (167,231,165) -- varchar, nvarchar, varbinary
     AND max_length = -1))

That's the easy way. But it does include the datatype hierarchyid. I'm not sure if indexes on hierarchyid columns can be rebuilt online or not. If you want to exclude those you can do it like this.

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)
 OR (c.user_type_id IN (129,130) ) )  -- geometry, geography

Answer 3

Dear My friend this Issue i faced it and i know why this problem is Coming and i think it's bug in Microsoft SQL Server , I Use SQL Server 2012 SP1 Last Updated patch the Problem : When you Create any table and make any Column as datatype one of this data types (text, ntext, image or FILESTREAM, varchar(max), nvarchar(max), varbinary(max)) at the first this Column with this Data type you cannot create index on it in SQL 2012 or include it in Index in 2008 you can but now no the Problem coming when you Create Clustered Index in any Column with any data type like INT when you Need to rebuild this Clustered index With online = on if the table have column With this any one from this data type ((text, ntext, image or FILESTREAM, varchar(max), nvarchar(max), varbinary(max)) The rebuild will Fail by this Error

Msg 2725, Level 16, State 2, Line 1 An online operation cannot be performed for index 'PK_Tickets' because the index contains column 'TicketBody' of data type text, ntext, image or FILESTREAM. 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.

So the salutation

Don't make rebuild to the Clustered index on the table that's have any Column with data type one of this data type (text, ntext, image or FILESTREAM, varchar(max), nvarchar(max), varbinary(max))

Example :

----Create Table 
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_3](
    [ID] [int] NOT NULL,
    [TicketBody] [ntext] NULL,
    [TicketChannel] [int] NULL,
 CONSTRAINT [PK_Table_3] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

--- rebuild Index 

ALTER INDEX PK_Table_3 ON Table_3 rebuild With 
(online = on) 

TRY TO CREATE NON-CLUSTERED INDEX IT WILL SUCCEED BUT CLUSTERED INDEX WILL FAIL

THANKS

ENG.MOSTAFA ELMASRY DATABASE ADMINISTRATOR / DB ANALYSIS