Rebuild SQL 2000 indexes on-line

I’ve asked several “experts” and none of them have a way to do this. Do you
know of any way to do this while the users are on line (without using DBCC
Indexdefrag)? As I understand the Indexdefrag, eventually you’ll have to
rebuild the indexes anyway.
I have 3 databases and each of them has several very large tables (one of
them exceeds 182 million rows). In order to truly be 7x24, I need to find a
way to rebuild the indexes while the users are in the database.
Thanks in advance for any help you can provide,
Regards,
George
You can't with SQL 2000. The best you can do without disrupting users
is to use DBCC INDEXDEFRAG, which will reorder pages in the index but
not shuffle extents so that they're contiguous. It will improve the
logical fragmentation as reported by DBCC SHOWCONTIG but not the extent
fragmentation.
I guess your options are 1) to upgrade to SQL 2005, which allows online
index rebuild operations (although there are a few caveats around those,
for example they're only available with the Enterprise Edition, can't be
done on clustered indexes when the underlying table contains LOB data,
can't be done on nonclustered indexes that contain LOB columns...), or
2) have an outage while you rebuild your clustered index on your large
tables (nonclustered indexes are normally not as big a problem).
Personally, I'm looking for a good excuse to rebuild one of our big
clustered indexes online in SQL 2005 just for the fun of it...and
because we can. <g>
*mike hodgson*
http://sqlnerd.blogspot.com
George wrote:

>I’ve asked several “experts” and none of them have a way to do this. Do you
>know of any way to do this while the users are on line (without using DBCC
>Indexdefrag)? As I understand the Indexdefrag, eventually you’ll have to
>rebuild the indexes anyway.
>I have 3 databases and each of them has several very large tables (one of
>them exceeds 182 million rows). In order to truly be 7x24, I need to find a
>way to rebuild the indexes while the users are in the database.
>Thanks in advance for any help you can provide,
>Regards,
>George
>