I applied page compression to a table in my 2008 r2 database (Enterprise). The table size was reduced by about 3GB. Should I see this reduction in the overall size of the database as well?
You won't see the physical size of the data file be reduced. You will see that the unused space within the file has gone up.
Ken Simmons has a useful query for viewing used vs. allocated space within database files - http://www.mssqltips.com/sqlservertip/1510/script-to-determine-free-space-to-support-shrinking-sql-server-database-files/
If you think this database will never grow now that you are compressing it, you could shrink the file to give some of the 3GB back to the OS.
If you can work with the current file size, you could should leave it alone and let the database fill the internal empty space without having to auto-grow the file to get back the spaced you would have released with a shrink.
answered Feb 14, 2014 at 05:13 PM