December 14, 2006 at 7:59 am
I was trying out the rebuild index task within the SQL 2005 maintenance plan. There is a checkbox "Keep index online while reindexing". I tried that option out.
The first table in my database looks like this:
CREATE TABLE [dbo].[CustomTemplates](
[Id] [int] NOT NULL,
[Title] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
-- <other fields. . .>
CONSTRAINT [CustomTemplates_PK] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
And the error I get when I run the underlying SQL Agent job is:
Executing the query "ALTER INDEX [CustomTemplates_PK] ON [dbo].[CustomTemplates] 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 'CustomTemplates_PK' because the index contains column 'Content' 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 index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I'm reading this right, the online index operation using the "ALTER INDEX" statement can fail on ANY table with a clustered index. Is that correct? If so, that seems pretty lame. The option seems virtually useless.
- john
December 14, 2006 at 8:18 am
there are certain conditions that need to be considered when performing this operation.. outlined here http://msdn2.microsoft.com/en-gb/library/ms190981.aspx
Thanks!
Rich
December 14, 2006 at 11:25 am
If I'm reading this right, the online index operation using the "ALTER INDEX" statement can fail on ANY table with a clustered index. Is that correct? If so, that seems pretty lame. The option seems virtually useless.
You can't perfom online indexing when the table contains LOB data like TEXT, NTEXT, IMAGE...
MohammedU
Microsoft SQL Server MVP
December 15, 2006 at 7:06 am
You can't perfom online indexing when the table contains LOB data like TEXT, NTEXT, IMAGE...
this is true in Oracle as well I believe.
April 13, 2010 at 8:29 am
You could read on a book to understand index, how index is made and why these types cannot be in "alter index online operation".
Jason
http://dbace.us
😛
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply