☰

什么因素阻止了 MS SQL Server 数据库的萎缩？

tag-icon • tag-icon sql-server
什么因素阻止了 MS SQL Server 数据库的萎缩？

我有一个 9 GB 的 MS SQL Server 2000 数据库，其中实际数据约为 1 GB。它位于共享网络主机上。我需要减小大小以避免支付过高的托管费用。

我曾尝试使用数据库收缩和DBCC 收缩文件。它们都不会缩小数据库。（是的，我知道我实际上不应该使用它们。）

DBCC SHRINKDATABASE(数据库名称)返回：

DbId FIleId 当前大小 最小大小 已用页数 估计页数
23 1 1114808 128 1113824 1113824
23 2 63 63 56 56

DBCC 收缩文件(1)返回：

DbId FIleId 当前大小 最小大小 已用页数 估计页数
23 1 1114808 128 1113824 1113824

在常规属性页面中，大小显示为 8,709 MB。可用空间显示为 0 MB。在文件属性页面中，主文件的初始大小为 8,652 MB。

DBCC SHOWCONTIG（大表）返回：

- 已扫描页数................................: 807
- 已扫描范围................................: 103
- 范围开关................................: 102
- 平均每页数..................................: 7.8
- 扫描密度[最佳数量：实际数量].......: 98.06% [101:103]
- 逻辑扫描碎片....................: 0.37%
- 范围扫描碎片....................: 1.94%
- 每页平均可用字节数..................: 68.5
- 平均页面密度（全）.................: 99.15%

跑步sp_spaceused，我得到了这个：

数据库大小：9131.94 MB
未分配空间：377.95 MB

跑步sp_spaceusused 大表，我得到了这个：

行数：56,095
保留：6,419,736 KB
数据：252,656 KB
索引大小：4,640 KB
未使用：6,162,440 KB

跑步sp_spaceusused bigTable2，我得到了这个：

行数：2,791
保留：2,362,744 KB
数据：114,232 KB
Index_size：200 KB
未使用：2,248,312 KB

其余的桌子都很小。

我要求我的主机备份数据库，删除并以较低的初始大小重新创建它，然后恢复它。他们尝试了，这是他们的答案：“我们按照步骤操作，但并没有解决问题，顺便说一句，SQL 服务器扩大了数据库大小，目前大小超过 9 GB。”（在他们尝试这样做之前，数据库大小为 7 GB。）

我的数据库是否可能需要 8GB 的​​未使用空间？假设不需要，我还有哪些其他选项可以缩小它？

我能够从我的网络主机下载一份副本，以便在本地计算机上使用。我安装了 SQL Server 2008 的试用版并附加了数据库。我运行了 ALTER INDEX ALL ON BIGTABLE REORGANIZE、ALTER INDEX ALL ON BIGTABLE REBUILD 和 DBCC SHRINKDATABASE。什么都没有改变。我仍然从上面列出的各种命令中获得相同的基本响应。

答案1

此数据库的恢复模式是什么？在运行 DBCC_SHRINKDATABASE 之前，您必须备份事务日志。我猜恢复模式是完整？在大多数情况下，这可能是最好的。

您对此数据库的数据库备份频率是多少？上次运行事务日志备份是什么时候。

您也可以尝试 DBCC SHRINKDATABASE (DatabaseName，TRUNCATEONLY)；

答案2

当您让他们删除数据库，以较小的规模重新创建数据库，然后恢复数据库时，应该不会发生任何变化。恢复数据库时，SQL Server 所做的第一件事就是删除当前存在的数据库。

使用 DBCC SHRINKFILE 命令是正确的方法。您只需要获取 SQL 来移动一些数据即可。

您可能需要重建表上的索引以压缩数据页，从而允许 SQL Server 从表中删除一些可用空间。当您使用 DBCC SHRINKFILE 收缩物理文件时，它将在文件内移动数据页，以便所有空白页都在文件末尾，然后它可以释放数据页。但是，如果您的页面只是部分填满，那么 SQL Server 在使用此命令时不会将它们合并以填充更多页面。

但是，可以使用 reindex 命令来完成此操作（实际命令因所用 SQL Server 版本而异）。这将压缩一些记录（取决于创建表/索引时指定的填充因子级别）。

您还应该知道，SQL Server 2000 及更早版本在处理文本和 ntext 数据类型以及在数据页内移动数据时存在问题。这些旧版本的 SQL Server 会在数据文件周围留下数据碎片，而您无法清理这些数据碎片，因为它们与任何行都没有关联。

答案3

您的问题很可能与已删除的文本或 ntext 数据没有释放已删除的空间有关 - 这在页面的所有版本中都是相同的。

重建索引的建议是错误的——SHOWCONTIG 和 sp_spaceused 清楚地表明浪费的空间不在索引中。备份日志的建议也是错误的——sp_spaceused 显示额外的空间在数据文件中，而不是日志中。

表中是否有任何 text/ntext 列？如果有，那么在 2000 年及之前恢复此空间的唯一方法是创建一个新表，然后将数据导出/导入到其中。直到 2005 年，我才添加了 LOB 压缩来缩小并 ALTER INDEX ... REORGANIZE。

另外，请注意，shrink 中有两个错误实际上会扩大 text/ntext 数据占用的空间量 - 请确保您使用的是 SP4。

谢谢

答案4

大卫，

请您发布一下大表的定义？

<ignore ref="see comments" editor="self">导致表非常大且无法缩小的一个常见问题是数据类型或数据结构的选择不当。表可能使用固定大小的数据类型来存储可变大小的数据。例如，如果您有一个定义为 char(100) 的字段，并且许多条目少于完整的 100 个字符（甚至为 NULL），则表仍然需要所有 10 个字符的存储空间。在这种情况下，更好的选择是 varchar(100)，它只会存储数据，而不会不必要地填充数据。 </ignore>

其他一些可能性是，您的托管服务提供商可能正在使用 TRUNCATEONLY 或 NOTRUNCATE 限定符（从而不允许将数据移动到未分配的页面，完全偏离了您的请求的要点），或者他们可能错误地指定了目标百分比（目标百分比是相对于收缩操作后您想要存储的数据的可用空间量）。在这两种情况下，如果他们使用 GUI 来收缩数据库，您可以简单地询问他们发出的 SQL 命令，或者在发出收缩指令之前查看对话框的屏幕截图。

我希望这有帮助。

杰西卡

相关内容