We have a table in our database with over 200 million rows and a Clustered Primary Key Index which is over 100GB. The Primary Key is only so large because many more millions of rows have been archived from the table and the Index has not been rebuilt since. By rebuilding I'm hoping to reduce the size of the index considerably and free up a lot of space in our database.
The problem we have is the disk only has 20GB of free space and I'm worried how much will be temporarily required when rebuilding the index.
What is the best way to do this? Do I bite the bullet, rebuild the index and hope there is enough space? Or do I drop the PK and rebuild it from scratch?
The archiving job is done on a datetime column and archives everything older than a year. This should run in parallel with the primary key which is an autoincrement since the datetime column is also autopopulated when a row is created.
DB, Table and Index Details
Row Count: ~212,300,000
Data space: ~109,000 MB
Index space: ~517 MB
Recovery model: Full
Data file free space: 5,991 MB / 151,000 MB
Data file drive space: 18.7 GB
Log file free space: 2275 MB / 25000 MB
Log file drive space: 79 GB
tempdb free space: 7476 MB / 19232 MB
There is only one index on the table, which is the clustered primary key:
Key Columns: 1x bigint
We're using SQL Server 2008 R2 Standard Edition.