SQL 2005: Disk space taken by dropped columns

Question

I have a big table in SQL Server 2005 that's taking about 3.5 GB of space (according to sp_spaceused). It has 10 million records, and several indexes.

I just dropped a bunch of columns from it, such that the record length got reduced to a half, and to my surprise it took zero time to do that. Obviously, sp_spaceused was still reporting the same taken space, SQL server hadn't really done anything when dropping the columns, other than marking them as "dropped".

So I moved all the data from this table into another new table, truncated it, and moved all the data back, so that it'd get all reconstructed.

Now, after that, data is taking 2.8 GB, which IS less than before, but I expected a bigger drop.

Is it possible that the fact that this table originally had these columns is still leaving something there?
Was truncating it not enough? Should I drop it and create it again with the smaller column set?
Or is the data really taking 2.8 GB?

Thanks!

Answer 1

How did you calculate that "expected a bigger drop"? Note that the data comes in 8K pages, which means that even if individual rows are smaller, that does not always mean you need less pages to store them. For example (an extreme example), if your rows used to be 7.5K each, only one row per page would fit. You drop some columns, your row is 5K, but still it is one row per page.

Answer 2

You will need to rebuild the clustered index (assuming you have one - by default, your primary key is the clustered key).

ALTER INDEX (your clustered index) ON TABLE (your table) REBUILD

The data is really the leaf level of your clustered index - once you rebuild it, it will be "compacted" and the rows should be stored on much fewer data pages, reducing your database size, too.

If that doesn't help at all, you might also need to run a DBCC SHRINKDATABASE on your database to really reclaim the space. These two steps together should really get you some smaller database file!

Marc