December 2, 2006 at 3:55 am
Hi
I'm trying to set up the sql2005 online reindex option ( ALTER INDEX [blah] ON [dbo].[blah] REBUILD WITH ( ONLINE = ON ) )
I'm testing with a copy of the system that I want to do this with, which we recently converted from sql2000.
I get an error like this:
------------
Executing the query "ALTER INDEX [idx_blah] ON [dbo].[tbl_blah] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )" failed with the following error: "Online index operation cannot be performed for index 'idx_blah' because the index contains column 'report_data' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml.
------------
... But the index doesn't contain that column (it's an image type - it would be a very strange column to index), and neither does any other index on the table (there aren't any others).
I suppose I can get around this by scripting the reindex to do that one table offline, but I'd rather not - does anyone have any idea why this spurious error would be popping up?
Ken McSwain
December 2, 2006 at 9:56 am
In sql 2000 when you create a table with with LOB data type (text, ntext, image...) sql automatically adds a row to the sysindexes table with some index name ttablename...I think when you migrate that row also migrated...
In 2005 it will ad a row to sysindexes but it will not assign any name to it...
You can test it with the following code...
CREATE TABLE TEST (COL TEXT)
SELECT * FROM SYSINDEXES WHERE ID = OBJECT_ID('TEST')
From BOL
Online index operations, the following guidelines apply:
Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
Nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline.
Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.
Note:
Online index operations are available only in Microsoft SQL Server 2005 Enterprise Edition.
MohammedU
Microsoft SQL Server MVP
December 3, 2006 at 3:27 pm
i had the same thing in my test script. i'm going to rewrite it so it runs only against selected tables and indexes and the rest will get offline indexing.
December 3, 2006 at 3:52 pm
Thanks for comments, guys.
It really is that index causing the problem - I dropped it and the problem went away, then manually recreated it, and the problem came back, so i don't think it's a leftover from the 2000 conversion.
When I ran the alter index job with that index dropped, it hit the same error on another table, which is also dodgy - ie the index that reports the error contains no LOB columns, although the table does. In this case it's the Primary Key. Both problem indexen are clustered, so I might try unclustering them, and see if that makes a difference.
Otherwise i'll just have to exclude them in a script, i suppose. Neither of them are particularly important tables to keep online, but i suppose there will be others.
It sounds like it's actually a bug with the code that checks the index before doing the rebuild, but I can't find any references in the KB to known problems.
Ken
December 3, 2006 at 5:20 pm
It is the clustering. I read the errror message a bit more closely. It actually says:
For non-clustered index the column could be an include column of the index,
for clustered index it could be any column of the table.
I guess that means that online reindexing is generally incompatible with clustered indexes on tables which contain LOB columns. Now I just have to write a script that distinguishes those indexes and rebuilds them offline. (or un-cluster the indexes)
Ken
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply