Sql Server 数据库收缩实践_sql数据库收缩

​记录一下：有一个做了很久的项目，涉及近十几个数据库，数据量不大，业务压力不重，也非分布式部署。由于操作频繁且历史悠久，数据库文件越来越大，而云服务器硬盘有限，因此我们需要对数据库进行合理优化。

本次优化是在本地测试的。

一 影响数据库大小的因素

我们先看一张图，这是其中一个业务库A：

大小：8301M
可用空间：6373.84M

这说明 数据文件 .mdf 和 日志文件 .ldf 占用了近8G的硬盘空间，且在持续地增大，但这其中却有6G多的空间是没有被使用的。

一般常用影响数据库文件大小的因素有以下几种：

1. 数据量: 数据库中存储的数据量是影响数据库文件大小的主要因素。数据越多则占用空间越大。
2. 索引: 为了提查询效率而建的索引也是空间占用的主要因素之一。而且若长时间不进行重新组织与生成，索引碎片也会导致索引效率下降。
3. 数据类型: 不同的数据类型占用的空间不同。例如，VARCHAR类型的字段可以根据实际内容长度变化，而CHAR类型则总是占用固定长度的空间。
4. 临时表和表变量：临时表和表变量也是占用空间的，若临时表在建立后不在使用完毕后drop的话，那么它便会在tempdb中存在一段时间。

tempdb数据库是一个特殊的系统数据库，它用于存储所有会话和事务的临时数据，如临时表、表变量、存储过程的中间结果集以及优化器生成的一些内部临时工作表等。

5. 事务日志：事务日志记录了所有数据库操作的详细日志。大量的事务会导致日志文件迅速增长。
6. 数据库备份：备份策略也会影响数据库文件大小，因为备份操作可能会触发日志截断，从而影响数据库文件的实际大小。

若要进行数据优化呢，可以参考1-4进行酌情处理。

二 数据库收缩

SQL Server数据库收缩（Shrink Database）是一种数据库维护任务，用于减少数据库文件的大小。

1. 什么情况可以用数据库收缩呢？

1. 数据删除: 当数据库中删除了大量的数据后，表中会留下许多碎片空间，数据库收缩可以回收这些空间。
2. 数据迁移: 如果数据从一个数据库迁移到另一个数据库或表结构发生改变，收缩数据库可以减少因数据迁移而产生的空间浪费。
3. 存储优化: 在某些情况下，如果数据库文件占用的空间远大于实际数据的大小，收缩操作可以帮助优化存储空间的使用。
4. 文件空间管理: 如果需要重新分配或重新组织数据库文件的空间，收缩操作可以作为一个步骤来实现这一目标。
5. 恢复空间: 在数据库恢复操作后，可能会有未使用的空间，收缩数据库可以帮助恢复这部分空间。

2. 注意事项

数据库收缩是一个有争议的操作，因为它有一些潜在的缺点：

• 性能影响: 收缩数据库可能会导致数据页的重新组织，这可能会影响查询性能，尤其是在收缩操作期间，因此建议在脱产环境中实施。

在执行收缩操作前后，监控数据库的性能和空间使用情况

• 碎片问题: 频繁的收缩操作可能会导致数据和索引碎片化，这会降低数据库的性能。
• 日志空间: 收缩操作会产生大量的日志记录，这可能会填满事务日志文件。之后可以重新检查数据库的索引及考虑日志文件收缩工作。
• 自动收缩: 不建议开启SQL Server的自动收缩功能，默认是禁用的。
• 影响数据恢复：数据库日志收缩本身不会破坏数据的完整性，但可能会影响在出现故障时恢复数据的能力。

在某些情况下，可能更倾向于使用其他方法来管理数据库空间，如重新组织索引或使用数据库压缩功能。因此，在决定是否执行数据库收缩时，应该仔细考虑其潜在的影响，并在必要时寻求专业建议。

3. 实操

环境：Sql Server 2016
操作步骤：选择数据库A ——>【右键：任务】——>【收缩】——>【数据库】。

• 为了不影响性能，我未勾选下边的重新组织文件。直接确定即可。

4 收缩结果

如图所示，我们做一个对比，超预期！

5 备注

收缩成功后，访问数据库是遇到异常：

……偏移量为0x00000001a9a000 的位置执行 读取 期间，操作系统已经向SQL Server 返回了错误21(设备未就绪。)……
解决方案：
重启SqlServer服务。
操作：选择sql服务器——>【】

三 附：日志文件收缩

已数据库 DB_NameA为例：

1. 数据库改为简单模式

USE[master]
GO
ALTER DATABASE [DB_NameA]  SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [DB_NameA]  SET RECOVERY SIMPLE   --简单模式
GO
1
2
3
4
5
6

2. 数据库改为简单模式

USE [DB_NameA]  
GO
DBCC SHRINKFILE (N'DB_NameA_log' , 1, TRUNCATEONLY)  --DB_NameA 的日志文件名 .ldf
GO
1
2
3
4

3. 数据库还原为完全模式

USE[master]
GO
ALTER DATABASE [DB_NameA]  SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE  [DB_NameA] SET RECOVERY FULL  --还原为完全模式
GO
1
2
3
4
5
6