How to reclaim space after turning on page compression in SQL 2008?

Question

I have just turned on page compression on a table (SQL 2008 Ent) using the following command:

ALTER TABLE [dbo].[Table1] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = PAGE
)

The hard drive now contains 50GB less space than before. I'm guessing that I need to run a command to reclaim the space. Anyone know it?

Answer 1

Here is a great article I found using google. It might have some answers for you. http://www.devx.com/SQL_Server/Article/41171/0/page/2

Answer 2

Have you checked using the table size using sp_spaceused?

Disk space used does not equal space used by data. The compression will have affected log file size (all has to be logged) and required some free working space (like the rule of thumb that index rebuild requires free space = 1.2 times largest table space).

Another option is that you need to rebuild the clustered index because it's fragmented. This compacts data and is the only way to reclaim space for text columns.

Also, read Linchi Shea's articles on data compression

Answer 3

I feel embarrassed even asking this question, but is it something that could be fixed by shrink the database in question? As it compressed the pages, perhaps it left the space free all throughout the file, and the data files just need to be condensed and shrunk to reclaim the space...

If it created a new, compressed copy of the table and then removed the old one from the file, but didn't shrink the file internally, this might also explain your sudden lack of space on the drive as well.

If this is the case, then a simple "DBCC SHRINKDATABASE('my_database')" should do the trick. NOTE: This may take a long time, and lock the database during that time so as to prevent access, so schedule it wisely.