Reindexing Clustered Primary Key We need to reindex a Clustered Primary Key on quite a large table. Here is the definition of the table:


Data Size: 22GB
Index Size: 34GB
Total Non-Clustered Indexes: 3
Row Count: 54 Million
Data File Free Space: Minimal
Drive Free Space: 3GB


The indexes are all over 90% fragmented. 

I know that reindexing the PK will lock the table, so I was wondering what best practice for this operation is? Since there is insufficient free space in the data file or disk drive to rebuild the index, I wrote a script which would:


Set the DB Recovery mode to Simple (to avoid growing the Log file too large)
Drop the non-clustered indexes
Rebuild the PK
Recreate the non-clustered indexes
Set the DB Recovery mode back to Full


I did a dummy run on a backup of the database on another server, which took 32 minutes in total, which is acceptable for a maintenance window for us.

The benefits I can see of dropping the non-clustered indexes are:


The DB file does not need to autogrow to rebuild the PK as space would have been created when the indexes are dropped
We will not need to shrink the DB file again (and cause additional fragmentation) following the maintenance
The non-clustered indexes will also be defragmented when they are rebuilt.


This script suits our needs for now, but what is considered best practice usually for a table of this size assuming we had infinite available space?
I know that reindexing the PK will lock the table, so I was wondering what best practice for this operation is?


Well having some DB file size/growth and free disk space monitoring would be good so this situation never happens. As you are in this situation I think the plan you have outlined is your only choice until you get more disk space and you really need more disk space. 


  This script suits our needs for now, but what is considered best
  practice usually for a table of this size assuming we had infinite
  available space?


Regular index maintenance would be nice. Try looking at Ola Hallengren's maintenance scripts here to solve that.  These scripts provide an almost fully automated methodology for maintenance of your SQL Server databases.