Adding disks when devices are full

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.