Disk Space Considerations for Rebuilding a large Clustered Primary Key Index

Question

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?

Archiving Details
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
Sort: Ascending

We're using SQL Server 2008 R2 Standard Edition.

Answer 1

The clustered index "is" the table. Rebuilding the index will require enough space to duplicate each existing non-deleted row, page-by-page. Since you don't have Enterprise Edition, you do not have the luxury of rebuilding the index online. This means the table will not be accessible during the rebuild operation.

You'd likely be better off rebuilding by creating a duplicate empty table on a different drive that has plenty of free space with room for foreseeable growth, moving data row-by-row in key order, then renaming the old and new tables. For instance if the original table is Table1 and the new copy is Table2, rename Table1 as Table1-old then rename Table2 as Table1.

Answer 2

If you can afford significant downtime, you could create a new table with the new PK and populate it in batches. Insert a million records, delete these from the old table, repeat until all records have been moved. Then drop the old table and rename the new.

This should require a modest amount of temporary space, but it may take a while. The old table would have to be offline during the process. Be sure you move records in the order they'll appear in the new PK.

If you're not familiar with looping and batching in SQL, I can sketch out sample code.

Note that it would probably be faster to populate the new table without a clustered key and then add the PK, but this would probably also require more temp space.