Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
And the type of the column is.....? Please post the table schema. –  Jon Seigel May 7 '13 at 2:22
1  
Also, please post the index definition –  swasheck May 7 '13 at 2:30
    
Also also, if I understand you correctly, when you run that query by Aaron, I'd expect you to not get any results because you're necessarily eliminating anything with the offending column type, right? –  swasheck May 7 '13 at 2:33
    
@swasheck If I was doing that, it would by c.system_type_id NOT IN in that query. –  Aaron Mason May 7 '13 at 6:45
1  
Upgrade to SQL Server 2012 –  Remus Rusanu May 7 '13 at 9:38
add comment

3 Answers

up vote 5 down vote accepted

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

share|improve this answer
    
There we go. That was just what I needed to hear. Seems the devil was in the details - 'MEMO' was not a column type, but a column of the table - which is of type ntext. I feel a bit of a fool, but at least I've learned something. Thanks @Justicator. –  Aaron Mason May 7 '13 at 23:19
add comment

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
share|improve this answer
    
Thanks Kenneth. I added those to the query, and still got no results. Now, since it is perfectly clear that there are no MEMO type fields, no MAXed character fields and no spatial fields, we can eliminate that as a cause for this error. Which leaves me with the question - what else could be causing this to fail? –  Aaron Mason May 7 '13 at 6:47
    
To be fair, the question I answered didn't specify a version, so I assumed 2005+. –  Aaron Bertrand Aug 4 '13 at 17:29
    
@AaronBertrand That's pretty reasonable, I just have a fair number of 2005+ instances that are holding on to legacy text, ntext etc with grim determination. –  Kenneth Fisher Aug 5 '13 at 3:28
add comment

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

share|improve this answer
2  
On 2012 just don't use any of text, ntext, image. They are all deprecated. Use varchar(max), nvarchar(max), varbinary(max) instead. As long as the column isn't FILESTREAM then you can rebuild the CI online (This is a change from 2008 where varchar(max), nvarchar(max), varbinary(max), xml also block online rebuilds) –  Martin Smith Aug 4 '13 at 9:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.