Post reply

Rebuild Index Maintenance Plan

  • March 23, 2012 at 1:56 am

    #270544

    Hi Guys

    When using a Maintenance plan to rebuild indexes ONLINE, I noticed that it handles LOB's by just not rebuilding the Index.

    Does anyone know if SQL 2005 handles it the same way? Or does it fail when it gets to a LOB column Index when running it online?

    Thanks

  • March 23, 2012 at 6:23 am

    #1463306

    In my experience, a 2005 ALTER INDEX REBUILD ONLINE will fail if it finds a LOB column. I have a script that checks alloc_unit_type_desc in sys.dm_db_index_physical_stats to decide whether an index can be rebuilt online. If that column = 'LOB_DATA' then you must rebuild the index offline.

  • March 23, 2012 at 6:27 am

    #1463307

    Ken Davis (3/23/2012)

    In my experience, a 2005 ALTER INDEX REBUILD ONLINE will fail if it finds a LOB column. I have a script that checks alloc_unit_type_desc in sys.dm_db_index_physical_stats to decide whether an index can be rebuilt online. If that column = 'LOB_DATA' then you must rebuild the index offline.

    I created a Maintenance Plan on SQL 2008 and I noticed that it ignores the Indexes that have LOB columns and doesn't attempt to rebuild them

    I'm trying to find out if SQL 2005 does the same thing or if it fails??

    Thanks

  • March 23, 2012 at 6:42 am

    #1463318

    I don't know the answer to that. If I remember correctly a rebuild index maint plan step will rebuild all indexes regardless of their fragmentation. Have I got that right? I prefer a custom script. With it you can check fragmentation and choose to do nothing, reorg, or rebuild, and you can do that online or offline based on the presence of LOB columns.

  • March 23, 2012 at 6:45 am

    #1463321

    Ken Davis (3/23/2012)

    I don't know the answer to that. If I remember correctly a rebuild index maint plan step will rebuild all indexes regardless of their fragmentation. Have I got that right? I prefer a custom script. With it you can check fragmentation and choose to do nothing, reorg, or rebuild, and you can do that online or offline based on the presence of LOB columns.

    Yes, the Maintenance Plan rebuilds everything regardless of Fragmentation Levels, Page Count etc..

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

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