作者:暮迟_MCz_P | 来源:互联网 | 2023-10-12 12:30
我将大胆猜测并说您的数据库设置为使用完全恢复。您从未根据上图备份过日志(更不用说完整的日志了)。如果是这种情况,那么您的日志文件会越来越大,并且无法清除,因为您没有进行备份。
如果您有房间,请进行完整备份,然后进行日志备份并再次尝试收缩。应该工作正常。
最坏的情况(我的意思是最坏的,特别是如果这是一个生产数据库)是将数据库设置为 SIMPLE 恢复,收缩然后进行备份。即使您没有足够的空间正常收缩,这也应该有效。
此时请确保为数据库设置定期备份。
你能告诉我们日志与数据文件的空闲百分比是多少吗?您也可以查看 sys.databases 中的 log_reuse_wait_desc。
SELECT name, log_reuse_wait_desc FROM sys.databases
最后但同样重要的是,您是否尝试过TRUNCATeonLY
收缩选项?
DBCC SHRINKFILE (Adventureworks2008R2_Log, TRUNCATeonLY)
这可能有很多原因。我将提及所有可能的原因,然后定义解决问题的解决方案。
- 如您的
DBCC
输出所述,您没有足够的可用磁盘空间来缩小数据库文件。换句话说。您尝试将页面从数据库文件移动到新创建的文件。在此期间,页面存在两次。之后 SQL Server 将删除旧的数据库文件并将新创建的文件用于生产。您可能需要增加磁盘空间或按照下面提到的解决方案进行操作。
- 防止收缩的另一点可能是数据库锁(这不是你的情况)。如果数据库在 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”解决了这个问题