question

paws27284 avatar image
paws27284 asked

Compression Results

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?
sql-server-2008-r2compressiondatabase size
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
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/][1] 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. [1]: http://www.mssqltips.com/sqlservertip/1510/script-to-determine-free-space-to-support-shrinking-sql-server-database-files/
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Ken! The database will grow, but this particular table is a history table and won't grow at all.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.