SQL Server 数据库不收缩

2023-02-09
前端之家小编收集整理的SQL Server 数据库不收缩内容,希望整理的这个开发编程问题SQL Server 数据库不收缩能够快速解决!

代码问题

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

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

最坏的情况(我的意思是最坏的,特别是如果这是一个生产数据库)是将数据库设置为 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”解决了这个问题

问题答案

暂无找到可以解决该程序问题的有效方法,小编努力寻找整理中!

如果你已经找到好的解决方法,欢迎将解决方案带上本链接一起发送给小编。

小编邮箱:starryrocklee#gmail.com (将#修改为@)

如果觉得前端之家所整理的内容很不错的话,欢迎点击下方分享按钮,转发给身边开发程序员好友。

编程问答


CREATE FILE 遇到操作系统错误 5(访问被拒绝。)
如何在一个查询中删除具有公共前缀的多个表?
多语句 TVF 与内联 TVF 性能
由于可能丢失数据,无法转换该值
SQL Server 数据库不收缩