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.
devices2 and device3 are completely full, as shown in Figure 1-2.
Figure 1-2: A table with three partitions on three devices
In the example above, adding two devices, repartitioning the table to use five partitions, and dropping and recreating 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 cannot be created on the device. |
Devices 4 and 5 |
Each device has two partitions, and each is 100% full. |
Figure 1-3 shows these results.
Figure 1-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.
