SQL - defragmenting an index on a compressed table

Question

We have a database with 2 tables, one with hundreds of millions of rows (row size<1KB), another 14 million rows. Compression enabled on both. Database size was ~66GB. Everything worked fine.

Indexes were 75% fragmented. Coworker started REBUILD on both tables. It's been running for 4.5 hours now. The MDF is almost 150GB and LDF about 13GB and it keeps growing. We're about to run out of space.

What should we do? Wait for it to finish? Cancel query? Reboot SQL? Reboot server?

Answer 1

The process completed 7 hours into it, after consuming about 170GB for MDF file.

So the answer is:

1. Have plenty of disk space, close to what uncompressed data would be, or at least about 3x compressed;
2. Be prepared to increase disk space as needed, have IT around for it (either on a VM server or with a hot-swap physical box);
3. Always do one table at a time;
4. Be ready to wait a long time.

Hope this helps someone.