SQL Server Performance Forum – Threads Archive

Reindexing a very large table

I have a table that is about 180GB. I drop and recreate the indexes on it monthly. I remember hearing a long time ago that if you run dbcc dbreindex that it creates a copy of the table and then drops the old table. If this is true, then I don’t have room for two 180GB tables on the drive and it would fail. It would be nice though to just be able to reindex rather than dropping and recreateing them one by one. I have not been able to find any information saying that it copies the table and you need at least twice the disk space of the table. Can someone let me know if I’ll be ok reindexing that table? I’m using SQL 2000. Thanks,
Chris

May be you don’t need to recreate all indexes.
I suggest to read: http://www.sql-server-performance.com/tp_automatic_reindexing.asp Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

See if this helps:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

I realize that I have different options. I just can’t find anything stating whether or not dbreindex makes a copy of the table and then deletes the original. If that’s true, then I wouldn’t be able to run it. Does anyone know for a fact what is going on behind the scenes when you run dbreindex? Thanks,
Chris

Refer to the books online for further information in this regard. BOL refers:
DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints. This means an index can be rebuilt without knowing the table’s structure or constraints, which could occur after a bulk copy of data into the table.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>