January 25, 2010 at 11:44 am
Hey all,
I am fairly new here. i have a few tables which need frequent rebuilding of Indexes. I want to do the re-indexing when the tables are online. But some of the tables have the XML columns in the tables thus generating in an error. Here is the error:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'PK_tblXRefUpdaterRequest' because the index contains column 'XmlHeaders' 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.
Can someone please help me on how to re-index tables with Xml Columns keeping the tables Online?
Thanks
January 25, 2010 at 12:10 pm
You can't. Indexes that contain XML or the MAX datatypes must be rebuilt offline. That includes the clustered index of a table that has one of those columns in it.
You can try ALTER INDEX ... REORGANISE which is always an online operation, but it does not do everything that REBUILD does. If the indexes fragment slowly, it may be ok, but, since you've said they need rebuilding often, that's not the case.
I'd personally suggest seeing if the clustered index can be moved so that the table doesn't fragment so fast, but that's a big design decision.
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
January 25, 2010 at 12:17 pm
January 25, 2010 at 12:51 pm
Thank you sir...
That was helpful
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply