Space requirement for Re-indexing.

Question

Post reply

Space requirement for Re-indexing.

sa24

SSCrazy Eights

Points: 8027
More actions
September 22, 2004 at 9:02 am

#85390

One of our applications has run into the below scenario and the table needs to be reindexed. Can someone please advise on how much additional space would be required for re-indexing alone ? I don't primarily support this application and don't know much about it but the requirement is for re-indexing and not index defrag.
Table Size : 200 Gb
Index Size : 87 GB.
Free space : 5 GB

Viewing 7 posts - 1 through 6 (of 6 total)

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

xyz SSCommitted Points: 1653 More actions · Answer 1

i never had so little free space, but i'm thinking if you set database to simple model, will it work?

Ray Mond SSCertifiable Points: 7159 More actions · Answer 2

Assuming you are talking about running DBCC DBREINDEX on the clustered index, I recall from a document by Quest Software that SQL Server needs approximately 1.25 times the data space of the table you are working on. So, if you have 5 Gig free, you are safe to run DBCC DBREINDEX on any table less than 4 Gig in size.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

sa24 SSCrazy Eights Points: 8027 More actions · Answer 3

Thanks a lot for the reply. I agree that theoretically you need roughly 1.25 times the data space but i was wondering if someone had run into such a scenario and what would be the minimum amount required for this.

SQLBill SSC Guru Points: 51440 More actions · Answer 4

I just (9/21) ran DBCC DBREINDEX on one of my databases. I only did the indexes on two tables.

DBCC DBREINDEX (dbname, tablename)

The database was 32 GB before the two tables were reindexed. After the reindexing, the database was 55 GB in size (included free space caused by the reindexing).

I was running this to gather data prior to doing it on my 178 GB database.

Another fact (maybe helpful), one table was 11,767,552 KB prior to the reindex and took 40 minutes to reindex (Scan density = 98.2% so it wasn't very fragmented).

-SQLBill

sa24 SSCrazy Eights Points: 8027 More actions · Answer 5

Thanks a lot Bill, That would definetely help. I will try to push to get additional 200GB.(Considering some growth factor.)

SQLBill SSC Guru Points: 51440 More actions · Answer 6

Here's an update:

Ran DBCC DBREINDEX on my 182+ GB database. Reindexed one table (116,331,780 rows, 36.795 GB). I stopped all connections prior to running the command. The reindex took 2 hours 53 minutes and 29 seconds. The database size went from 182.470 GB (0.22867 GB free space) to 251.470 GB (40.142 GB free space). The transaction log increased to about 50 GB with 44 GB now being free space.

I'm not shrinking the database yet as I need to reindex a second table with a few billion rows. Based on the above reindex, this one is estimated at 4 hours. So I couldn't do both the same day.

-SQLBill