SQL Server 2005:删除的列占用的磁盘空间 [英] SQL Server 2005: disk space taken by dropped columns

问题描述

我在 SQL Server 2005 中有一个大表，它占用了大约 3.5 GB 的空间(根据 sp_spaceused).它有 1000 万条记录和多个索引.

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.

我只是从中删除了一堆列，这样记录长度就减少了一半，令我惊讶的是，它花了零时间来做到这一点.显然，sp_spaceused 仍然报告相同的占用空间，SQL Server 在删除列时并没有真正做任何事情，除了将它们标记为已删除".

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.

现在，在那之后，数据占用了 2.8 GB，比以前少了，但我预计会有更大的下降.

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?

或者数据真的需要 2.8 GB 吗?

Or is the data really taking 2.8 GB?

谢谢！

推荐答案

您将需要重建聚集索引(假设您有一个 - 默认情况下，您的主键是聚集键).

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.

如果这根本没有帮助，您可能还需要在您的数据库上运行 DBCC SHRINKDATABASE 以真正回收空间.将这两个步骤放在一起应该会让你得到一些更小的数据库文件！

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!

马克

这篇关于SQL Server 2005:删除的列占用的磁盘空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！