Post reply

rebuilding Indexes Online?

  • January 25, 2010 at 11:44 am

    #219850

    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

    Chitanya Chitturi :-)

  • January 25, 2010 at 12:10 pm

    #1108319

    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

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

  • January 25, 2010 at 12:17 pm

    #1108326

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

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 25, 2010 at 12:51 pm

    #1108343

    Thank you sir...

    That was helpful

    Chitanya Chitturi :-)

Viewing 4 posts - 1 through 3 (of 3 total)

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