I have just turned on page compression on a table (SQL 2008 Ent) using the following command:
我刚刚使用以下命令在表(SQL 2008 Ent)上打开了页面压缩:
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?
硬盘现在比以前少50GB的空间。我猜我需要运行一个命令来收回空间。谁知道呢?
2 个解决方案
#1
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.
如果是这种情况,那么一个简单的“DBCC SHRINKDATABASE('my_database')”应该可以解决问题。注意:这可能需要很长时间,并在此期间锁定数据库以防止访问,因此明智地安排它。
#2
Have you checked using the table size using sp_spaceused?
您是否使用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).
使用的磁盘空间不等于数据使用的空间。压缩将影响日志文件大小(所有必须记录)并且需要一些空闲工作空间(如经验法则,索引重建需要空闲空间= 1.2倍最大表空间)。
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
另外,请阅读Linchi Shea关于数据压缩的文章
#1
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.
如果是这种情况,那么一个简单的“DBCC SHRINKDATABASE('my_database')”应该可以解决问题。注意:这可能需要很长时间,并在此期间锁定数据库以防止访问,因此明智地安排它。
#2
Have you checked using the table size using sp_spaceused?
您是否使用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).
使用的磁盘空间不等于数据使用的空间。压缩将影响日志文件大小(所有必须记录)并且需要一些空闲工作空间(如经验法则,索引重建需要空闲空间= 1.2倍最大表空间)。
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
另外,请阅读Linchi Shea关于数据压缩的文章