合 MSSQL收缩数据文件和日志文件

简介

事务日志记录着在相关数据库上的操作，同时还存储数据库恢复(recovery)的相关信息。收缩日志的原因有很多种，有些是考虑空间不足，有些则是应用程序限制导致的。

当数据库随着时间而越来越大时，可对数据库进行收缩操作；收缩数据文件通过将数据页从文件末尾移动到更靠近文件开头的未占用的空间来恢复空间，在文件末尾创建足够的空间后，可取消对文件末尾的数据页的分配并将它们返回给文件系统；

SQL Server 数据库支持自动收缩数据库和用户手动收缩数据库两种方式；

自动收缩

为提高数据库的使用效率，SQL Server 会寻找可用的数据库并找出第一个配置为自动收缩的数据库，它将检查该数据库，并在需要时收缩该数据库；待一个数据库收缩完成后，系统会等待几分钟再检查下一个配置为自动收缩数据库，即 SQL Server 不会同时检查所有数据库，也不会同时收缩所有数据库，它将以循环方式处理各个数据库，以负载在时间上错开；若用户需要 SQL Server 数据库系统自动对数据库进行收缩，只需为该数据库设置自动收缩功能即可；操作步骤如下所示：

右击选择数据库 → 属性 → 在数据库属性对话框，选项 → 将自动收缩设置为 TRUE；

手动收缩数据库

用户还能手动对数据库进行收缩，但手动收缩数据库有一定的限制和局限；主要的表现有如下几个方面：

• 收缩后的数据库不能小于数据库的最小大小 ；最小大小在数据库创建时指定的大小，或者上一次使用文件大小更改操作设置的大小；
• 不能在备份数据库时收缩数据库；反之，也不能在数据库执行收缩时备份数据库；
• 遇到内存游湖的列存储索引时，DBCC SHRINKDATABASE 操作将会失败；遇到 columnstore 索引之前完成的工作将会成功，所以数据库可能会较小；若要完成 DBCC SHRINKDATABASE，则需要执行 DBCC SHRINKDATABASE前禁用所有列的存储索引，再重新生成列存储的索引；

手动收缩数据库可在 SSMS 工具的对象资源管理器中完成，具体步骤如下：

• 展开数据库，右击要收缩的数据库；

• 任务 → 收缩 → 单击数据库

  

  

  在收缩数据界面展示的是数据库 srs 的基本信息，若需要进行收缩操作，需要先选中“在释放未使用的空间前重新组织文件。选中此项可能会影响性能（R）”，再收缩后文件中的最大可用空间中选择收缩后的空间，单击确定，即可完成手动收缩数据库操作；

手动收缩文件

• 手动收缩文件跟手动收缩数据库操作类似；

• 在 SSMS 工具对文件的对象资源管理器中完成，具体步骤如下：

  展开数据库，右击数据库；任务 → 收缩 → 单击文件

在收缩文件界面，在收缩操作中，可选择释放未使用空间、在释放未使用空间前重新组织页、通过将数据迁移到同一文件组中的其他文件来清空文件，三选一来进行手动收缩文件操作；

通过将数据迁移到同一文件组中的其他文件来清空文件

从指定文件迁移所有数据。 此选项允许使用 ALTER DATABASE 语句删除文件。 此选项等效于执行带有 EMPTYFILE 选项的 DBCC SHRINKFILE。

1.对数据库创建一个新的文件FileB，文件组为GroupA

2.使用数据库/任务/收缩/文件,选中FileA ,勾选择:通过数据迁移到同一文件组中的其他文件来清空文件

可能会报错“无法将文件 "lhrdb" 的所有内容移到其他位置，以完成清空文件操作。” 没有好的办法解决。。。。

3.等待……

4.收缩完成，执行ALTER DATABASE [数据库名称] REMOVE FILE [FileA]

问题在这一步出来,报错消息:无法对具有数据库快照或活动 DBCC 副本的数据库执行此操作。

解决办法：把该数据库的快照删除掉，再执行第4步，成功的把FileA文件删除掉。

SQL Server的full和simple之间是什么区别

SQL Server 中的“full”和“simple”是指数据库恢复模型，这两种模型决定了数据库如何进行日志记录和恢复。下面是它们之间的区别：

1. Simple模型：在简单恢复模式下，SQL Server仅保留当前活动事务的日志信息，一旦这些事务提交，相应的日志记录将被清除。这意味着，如果出现故障，只能将整个数据库还原到最近备份的状态，无法使用全量备份+日志追加恢复到最新时刻。简单恢复模式适用于仅需要在备份之间进行最小的数据丢失的数据库。
2. Full模型：在完整恢复模式下，SQL Server将保留所有事务的日志信息，包括已提交和未提交的事务。这允许数据库管理员使用日志备份文件逐步恢复数据库到发生故障之前的某个时间点。完整恢复模式适用于要求最小数据丢失和最小停机时间的数据库。

总之，简单恢复模式提供了更少的恢复选项，但通常比完整恢复模式需要更少的磁盘空间，而完整恢复模式可以提供更高级的恢复选项，但需要更多的磁盘空间。因此，应该根据数据库的需求和负担来选择适当的恢复模式。

SQL Server收缩数据文件需要注意什么

在 SQL Server 中，当数据库的数据文件(.mdf)中存在大量未使用的空间时，可以使用收缩操作将文件大小减小，从而节省磁盘空间。但是，在执行收缩操作时需要注意以下几点：

1. 收缩操作可能导致性能下降：收缩操作会移动数据文件中的数据，如果数据文件很大，则此操作可能需要很长时间，从而导致性能下降。因此，在执行此操作之前，应该在非高峰期执行，以避免影响生产环境。
2. 收缩操作可能导致索引碎片化：收缩操作会导致数据文件中的数据移动，因此可能导致表和索引的碎片化，从而影响性能。因此，在执行此操作之前，应该考虑使用索引重新组织或重建来修复任何可能的碎片化。
3. 收缩操作可能不会显著减少文件大小：在某些情况下，收缩操作可能不会显著减少数据文件的大小。例如，如果数据文件中存在大量未使用的空间，但是该空间包含有数据页，那么在收缩操作后，文件大小可能只会略微减小。
4. 收缩操作可能会造成数据损坏：在收缩操作期间，可能会发生磁盘故障、系统故障或其他异常情况，这可能导致数据损坏。因此，在执行此操作之前，应该备份所有数据，并确保备份的完整性。

总之，在执行收缩操作之前，应该了解其可能产生的影响和潜在风险，并在执行此操作之前备份所有数据。同时，应该在执行此操作之前，评估数据文件的大小和结构，以确保收缩操作可以产生预期的效果。

数据文件收缩后文件大小不变的原因

在 SQL Server 中，数据文件的收缩操作（使用DBCC SHRINKDATABASE或DBCC SHRINKFILE命令）旨在减小数据库文件的物理大小，从而释放未使用的空间，以节省磁盘空间。然而，在执行此操作后，文件大小可能没有显著减小，或者仍然与收缩操作之前的大小相同，原因可能有以下几个方面：

1. 未使用的空间比较分散：如果数据库中存在许多未使用的空间，但是这些空间在文件中分散，那么收缩操作可能无法将它们整理在一起，以便将其释放。这意味着在执行收缩操作后，文件大小可能不会显著减小。
2. 日志文件过大：如果事务日志文件(.ldf)过大，它可能会占用磁盘空间并阻止数据文件缩小。在这种情况下，可以使用DBCC SHRINKFILE命令来减小事务日志文件的大小。
3. 数据文件的碎片化：如果数据文件中存在大量的碎片化，那么在执行收缩操作之前，可以考虑使用索引重组或重建等操作来修复数据文件中的碎片化。这样，收缩操作就可以更有效地减小文件大小。
4. 自动增长设置：如果自动增长设置过大，那么即使收缩操作成功将文件减小到最小大小，文件大小也会随着自动增长操作而增加。因此，可以调整自动增长设置来减小文件的增长速度。
5. 文件空间限制：如果数据文件已达到文件系统的空间限制，那么即使收缩操作成功，文件大小也不会变化。

总之，收缩数据文件操作的效果取决于许多因素，包括数据库的大小、碎片化程度、日志文件的大小和自动增长设置等。在执行此操作之前，应该评估这些因素，并采取适当的步骤来减小文件的大小。

如果未使用的空间在数据文件中比较分散，可能需要采用一些特殊的方法才能彻底收缩数据库。以下是一些可能的方法：

1. 重建所有索引：使用DBCC DBREINDEX或ALTER INDEX命令可以重建数据库中的所有索引。这可以消除索引碎片，使数据在磁盘上更加连续，从而有助于收缩数据库。
2. 迁移数据库到新的文件组：如果数据库中的表和索引已经很大，可以考虑将它们迁移到一个新的文件组中，然后删除旧的文件组。在此过程中，可以选择调整文件组的大小和配置，以更好地满足数据库的需求。
3. 导出和导入数据：如果数据库中只有少量数据需要保存，可以考虑将其导出到一个文件中，然后重新创建一个新的数据库并将数据导入到其中。这可以消除所有未使用的空间，并使数据库更加紧凑。

无论使用哪种方法，重要的是在执行此类操作之前，确保对数据库进行备份并测试所有更改，以确保数据完整性和可用性。

DBCC SHRINKDATABASE 和 DBCC SHRINKFILE命令的区别有哪些

DBCC SHRINKDATABASE和DBCC SHRINKFILE命令都是在SQL Server中用于压缩数据库或日志文件的命令，但它们之间有以下区别：

1. 范围不同：DBCC SHRINKDATABASE命令可以压缩整个数据库，包括所有的数据和日志文件，而DBCC SHRINKFILE命令只能压缩一个指定的数据或日志文件。
2. 语法不同：DBCC SHRINKDATABASE命令使用数据库名称作为参数，而DBCC SHRINKFILE命令使用文件名称和大小作为参数。
3. 对数据和日志文件的处理方式不同：DBCC SHRINKDATABASE命令将压缩整个数据库，包括所有的数据和日志文件，而DBCC SHRINKFILE命令可以将日志文件或数据文件压缩到指定的大小。
4. 压缩效率不同：DBCC SHRINKFILE命令的压缩效率通常比DBCC SHRINKDATABASE命令高，因为DBCC SHRINKFILE只需要压缩单个文件，而DBCC SHRINKDATABASE需要同时处理多个文件。
5. 可能引起性能问题：DBCC SHRINKDATABASE和DBCC SHRINKFILE命令都可能引起性能问题。特别是在执行这些命令时，系统可能会出现阻塞和性能下降。因此，在执行这些命令之前，应该谨慎评估其影响并备份数据库。

总之，DBCC SHRINKDATABASE和DBCC SHRINKFILE命令都可以用于压缩数据库或日志文件，但它们的使用方式、范围和效率不同。在使用这些命令时，需要考虑其影响并选择合适的命令来满足需求。

收缩数据文件和日志文件的方法

方法1：使用命令（推荐）

通过修改恢复模式为“简单”，这种收缩日志的方法是不得以的方法，也是终极方法，在收缩之前，在完整模式下，进行备份；

方法2：通过图形界面逐步操作

下面介绍的是在简单模式下，进行收缩操作。

1、打开数据库属性窗口

2、更改数据库恢复模式，“完整”改成“简单”

3、收缩数据库日志，“任务”->“收缩”

4、收缩完，将数据库的恢复模式修改为“完整”

分批逐步收缩

若数据文件很大，则可以根据当前数据库数据文件大小，依次减少目标大小，执行下面语句进行收缩：

日志收缩示例

如果数据库的恢复模式为FULL（完整恢复模式），那么在数据库运行过程中会产生很多事务日志，若不清理，长年累月日志就会很大，如下的巡检输出结果：

日志有300多g，而其实数据库才30多G，所以需要收缩，收缩过程参考方法1。

收缩之后的效果：

这里需要说明的一点是：日志有300g，若是执行收缩（DBCC SHRINKFILE('test_Log' , 1）操作，其实也就是几秒钟的时间，所以不用担心。

DBCC SHRINKFILE的选项

参考：https://learn.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver16

target_size

整数，表示文件的新大小（以 MB 为单位）。

如果未指定或为 0，DBCC SHRINKFILE 缩小到文件创建大小。不过，DBCC SHRINKFILE 不会收缩已超过所需存储数据大小的文件。例如，如果使用 10 MB 数据文件中的 7 MB，则带有 target_size 为 6 的 DBCC SHRINKFILE 语句只能将该文件收缩到 7 MB，而不能收缩到 6 MB。

可以使用 DBCC SHRINKFILE <target_size> 缩小空文件的默认大小。 例如，如果创建一个 5 MB 的文件，然后在文件仍然为空的时候将文件收缩为 3 MB，默认文件大小将设置为 3 MB。 这只适用于永远不会包含数据的空文件。

如果 target_size 已指定，DBCC SHRINKFILE 会尝试将文件收缩到目标大小。 要释放的文件区域中的已用页移到文件保留区域中的可用空间。 例如，对于 10 MB 数据文件，target_size 为 8 的 DBCC SHRINKFILE 操作会将文件最后 2 MB 中的所有已用页移到文件前 8 MB 中的任何未分配页中。

DBCC SHRINKFILE的TRUNCATEONLY选项

TRUNCATEONLY选项将文件末尾的所有可用空间释放给操作系统，但不在文件内部移动任何页。 数据文件只收缩到最后分配的区。

如果使用 TRUNCATEONLY 指定，则会忽略 target_size。

TRUNCATEONLY 选项不会移动日志中的信息，但会删除日志文件末尾的失效 VLF。 FILESTREAM 文件组容器不支持此选项。

报错“不能收缩 ID 为 11 的数据库中 ID 为 1 的文件，因为它正由其它会话收缩或为空”

解决：

通过维护计划配置数据库自动收缩

1、SQL代理服务必须启动，且需设置为随系统自动启动，否则不能自动执行计划（开始--所有程序--Microsoft SQL Server 2008 R2--配置工具-- SQL Server 配置管理器--SQL Server 服务--SQL Server 代理--右键属性）启动服务，并参考下图设置为自动。

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。