SQLServer数据库不收缩

我将大胆猜测并说您的数据库设置为使用完全恢复。您从未根据上图备份过日志（更不用说完整的日志了）。如果是这种情况，那么您的日志文件会越来越大，并且无法清除，因为您没有进行备份。

如果您有房间，请进行完整备份，然后进行日志备份并再次尝试收缩。应该工作正常。

最坏的情况（我的意思是最坏的，特别是如果这是一个生产数据库）是将数据库设置为 SIMPLE 恢复，收缩然后进行备份。即使您没有足够的空间正常收缩，这也应该有效。

此时请确保为数据库设置定期备份。

你能告诉我们日志与数据文件的空闲百分比是多少吗？您也可以查看 sys.databases 中的 log_reuse_wait_desc。

SELECT name, log_reuse_wait_desc FROM sys.databases


最后但同样重要的是，您是否尝试过TRUNCATeonLY收缩选项？

DBCC SHRINKFILE (Adventureworks2008R2_Log, TRUNCATeonLY)


这可能有很多原因。我将提及所有可能的原因，然后定义解决问题的解决方案。

1. 如您的DBCC输出所述，您没有足够的可用磁盘空间来缩小数据库文件。换句话说。您尝试将页面从数据库文件移动到新创建的文件。在此期间，页面存在两次。之后 SQL Server 将删除旧的数据库文件并将新创建的文件用于生产。您可能需要增加磁盘空间或按照下面提到的解决方案进行操作。


2. 防止收缩的另一点可能是数据库锁（这不是你的情况）。如果数据库在 DDL 更改期间被锁定，它将无法收缩数据库文件。如果您使用向导缩小文件，您可能会不时遇到超时消息。如果您将它作为脚本运行，它通常会一直等待直到锁被释放，然后收缩文件。

最简单的解决方案是计划！检查您的数据库并尝试评估每天将插入多少数据，确定增长率。默认情况下，SQL Server 会将文件增大 10%。这对于只有 100MB 的数据库来说还不错，因为增长仅为 10MB。但是，如果您的数据库变得越来越大，增长将耗费大量资源和时间（100GB -> 10 GB，1TB -> 100GB 等）。以 MB 为单位定义增长因子。它不应该太小（例如 1MB），因为它会在大事务中给您的磁盘带来压力，并会减慢您的操作，但也不能太大而导致巨大的写入操作（例如 10GB）。

另外定义可能升级的数据库的增长限制（例如日志数据库）。如果达到限制，事务可能会失败，但不会对日志记录数据库造成太大影响。但是，如果您的日志记录数据库用完了所有可用的磁盘空间，则可能会导致生产数据库上的事务中止/回滚。

如果您遇到问题，即您的磁盘空间太小，无法再次容纳已用磁盘空间（例如：140GB 文件，40GB 可用空间，意味着 100GB 用于重组 -> 您至少需要 100GB 可用空间用于移动操作收缩的时间），您仍然可以通过使用TRUNCATeonLY标志来收回一些空间。

DBCC SHRINKFILE (1,TRUNCATeonLY)


这将切断所有保留在 file_id = 1 的数据库文件末尾的空数据页。回到我们的 140GB 示例：如果您的 140GB 数据库文件在 120GB 的位置保存最后写入的页面，您将获得最后 20GB 几乎立即恢复。这个过程非常快，因为它只是将文件结束标记移动到 120GB 的位置，并将通知您的操作系统有关可用空间的信息。

您可以使用此查询快速了解数据库文件：

SELECT *
FROM sys.sysfiles


如果您有足够的可用磁盘空间（或者在您TRUNCATeonLY对每个文件运行之后），您应该能够真正清理您的数据库文件并真正缩小它们。

这可以通过使用以下语句来实现：

DBCC SHRINKFILE (1);


如果您有一个经常写入或经常运行更大事务的数据库表，我建议定义一个目标限制。这取决于您使用该数据库的经验。通常为 5-10%（取决于数据库的大小），但最小值应设置为 100-1000MB。以我们的 140GB 数据库文件（40GB 空）为例，目标大小应设置为 110GB（100GB 已用 + 10% 保留）。

您可以使用以下语句实现此目的：

DBCC SHRINKFILE (1,112640); -- 110GB in MB


但请注意，这可能会运行很长时间（取决于您的文件大小和 I/O 子系统）。由于您将在 I/O 子系统上创建更大的工作负载，因此我建议在低负载时间（可能是一夜之间）运行此类脚本。

如果你真的想清理你的数据库文件，检查所有索引和REBUILD/或REORGANIZE所有具有高碎片的索引是个好主意。

如果你在开发机器上或者真的愿意冒险，你可以运行这个语句。它将遍历数据库中的每个索引并重建它。

DeclARE @sql nvarchar(max), @sch nvarchar(max), @obj nvarchar(max), @ind nvarchar(max)
DeclARE cur CURSOR FOR
SELECT s.name, o.name, i.name
FROM sys.objects as o
INNER JOIN sys.schemas as s
ON o.schema_id = s.schema_id
INNER JOIN sys.indexes as i
ON o.object_id = i.object_id
WHERE i.type > 0
OPEN cur
FETCH NEXT FROM cur INTO @sch, @obj, @ind
WHILE @@FETCH_STATUS = 0 BEGIN
SET @sql = N'ALTER INDEX ['+@ind+'] ON ['+@sch+'].['+@obj+'] '
+ N'REBUILD PARTITION = ALL '
+ N'WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, '
+ N'OnLINE= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
PRINT(@sql) EXEC (@sql)
FETCH NEXT FROM cur INTO @sch, @obj, @ind
END
CLOSE cur
DEALLOCATE cur


在该过程之后，您的数据库文件可能会再次增长。但是您可以将其缩小到更小的文件大小，因为每个索引中的可用空间都被删除了。

收缩数据库文件时要小心。SQL Server 提前分配文件空间是有充分理由的。磁盘分配很昂贵，尽可能少做是个好主意。如果您有一个非常敏捷的数据库，每天产生几 GB 的开销并在一段时间后释放它，那么总是将文件缩小到最小值并不是一件好事。您浪费光盘能量来增大文件，做您的工作并再次浪费光盘能量以再次缩小文件。在这种情况下，请注意浪费一点磁盘空间来改善您的数据操作。

希望这个冗长的总结能帮助您解决问题，甚至帮助您制定数据库计划。

在 SQL Server 2017 上，查询尝试包含一些系统表。在“WHERE i.type > 0”下面添加“AND o.is_ms_shipped = 0”解决了这个问题