The result of creating a clustered index when a physical device is completely full is that two partitions are created on one of the other physical devices. Figure 5-2 and Figure 5-3 show one such situation.
Devices 2 and 3 are completely full, as shown in Figure 5-2.
Figure 5-2: A table with 3 partitions on 3 devices
Adding two devices, repartitioning the table to use five partitions, and dropping and re-creating the clustered index produces the following results:
Device 1 |
One partition, approximately 40% full. |
Devices 2 and 3 |
Empty. These devices had no free space when create index started, so a partition for the copy of the index could not be created on the device. |
Devices 4 and 5 |
Each device has two partitions, and each is 100% full. |
Figure 5-3 shows these results.
Figure 5-3: Devices and partitions after create index
The only solution, once a device becomes completely full, is to bulk copy the data out, truncate the table, and copy the data into the table again.
