Online index operation cannot be performed for index

Question

Post reply

Online index operation cannot be performed for index

changbluesky

SSC Eights!

Points: 807
More actions
October 19, 2008 at 11:44 pm

#127181

I have a table SHPLBL
CREATE TABLE [dbo].[SHPLBL](
[SOLDTO] [varchar](10) ,
[LBLTYP] [varchar](30) ,
[CONTENT] [nvarchar](max)
)
and carete a clustered index
CREATE UNIQUE CLUSTERED INDEX [ix_shplbl] ON [dbo].[SHPLBL]
(
[LBLTYP] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
But when I rebuild the index ix_shplbl,
ALTER INDEX [ix_shplbl] ON [dbo].[SHPLBL] REBUILD WITH ( PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON )
it will show the error msg:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'ix_shplbl' 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.
Why the clustered index ix_shplbl cannot be rebuilt online when column CONTEN type is varchar(max)?
So--confusing...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

froah SSC Journeyman Points: 97 More actions · Answer 1

From here:

https://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3774841&SiteID=1

---------------------------------

In Books Online it says like this.

"Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:"

"Clustered indexes if the underlying table contains LOB data types"

"Nonclustered indexes that are defined with LOB data type columns"

"Nonclustered indexes can be rebuilt online if the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey columns."

http://msdn.microsoft.com/en-us/library/ms188388.aspx

I have a stored procedure that supports this logic. Please use it if you like.

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

Reference By-http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3659724&SiteID=1

Ola Hallengren

---------------------------------

Assuming:

LOB data type columns: text, ntext, image, varchar(max), nvarchar(max) and varbinary(max)

Regards

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

It's just a limitation on online rebuilds. You cannot do an online rebuild of a clustered index if the table contains any LOB data (text, ntext, image, varchar(max), nvarchar(max), varbinary(max))

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

One way around this is to move all 'prevent' columns such as varchar(max) off to another table with a one-to-one link back to the base table. Then a simple join gets you what you need and you can then rebuild the master table index online. You could also decide to switch to a NON-clustered index here, which should be able to rebuild online since it would not contain the 'bad' column. Not 100% certain about that last part though.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

TheSQLGuru (10/21/2008)
You could also decide to switch to a NON-clustered index here, which should be able to rebuild online since it would not contain the 'bad' column. Not 100% certain about that last part though.

Yup. The NC should be fine to rebuild online, providing it doesn't INCLUDE the varchar(max)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Don Halloran SSCertifiable Points: 5937 More actions · Answer 5

To reopen this topic briefly, I'm not clear on whether a rebuild is not allowed with a varchar(max) if sp_tableoption has been used to force the data to be stored off row. Does the limitation only apply to LOB data that is stored in row if using 2005+ LOB types?

noeld SSC Guru Points: 96590 More actions · Answer 6

it does not really matter if the LOB is in-row or not. if there are LOB columns you hit the limitation.

* Noel