SQL Server 在运用过程会产生大量的日志造成日志文件不断增长，但实际上有效的日志只占据10%不足的空间。无效的日志未能及时被清理，而导致占据日志文件空间引起日志文件膨胀。日志文件在膨胀后，如过不实施维护操作并无法自动缩小。

数据库收缩将会收到恢复模式设置差异，它们差异参考如下：
简单模式收缩： 将所有的提交完成（或者说所有有效的日志）都可以删除。重新定位到新的日志之初。

完整模式收缩： 保留有效的日志

sql Server 收缩数据库 sql语句 sqlserver收缩数据库影响_日志文件


当计划收缩数据库时，请考虑以下信息：

  • 在执行会产生许多未使用空间的操作（如截断表或删除表操作）后，执行收缩操作最有效。
  • 大多数数据库都需要一些可用空间，以供常规日常操作使用。 如果反复收缩数据库并注意到数据库大小变大，则表明收缩的空间是常规操作所必需的。 在这种情况下，反复收缩数据库是一种无谓的操作。
  • 收缩操作不会保留数据库中索引的碎片状态，通常还会在一定程度上增加碎片。 这是不要反复收缩数据库的另一个原因。
  • 除非有特定要求，否则不要将 AUTO_SHRINK 数据库选项设置为 ON。

sql Server 收缩数据库 sql语句 sqlserver收缩数据库影响_数据库_02


收缩任务有两种方式：

收缩数据库：通过收缩所有数据文件释放未使用的空间，可以减小数据库的大小。

sql Server 收缩数据库 sql语句 sqlserver收缩数据库影响_日志文件_03


收缩文件：通过收缩单个文件释放未分配的空间，可以减小数据库的大小。通常用在数据库日志文件收缩。

查询所有数据库的日志文件大小：

dbcc sqlperf(logspace)
Database Name                                                                                                                    Log Size (MB) Log Space Used (%) Status
-------------------------------------------------------------------------------------------------------------------------------- ------------- ------------------ -----------
master                                                                                                                           1.992188      46.10294           0
tempdb                                                                                                                           7.992188      17.85191           0
model                                                                                                                            7.992188      16.56891           0
msdb                                                                                                                             31.67969      10.12947           0
zhong                                                                                                                            7.992188      11.79741           0
zhong_test                                                                                                                       7.992188      27.29106           0

sql Server 收缩数据库 sql语句 sqlserver收缩数据库影响_日志文件_04

[注意事项]

  1. 收缩后的数据库不能小于数据库的最小大小。 最小大小是在数据库最初创建时指定的大小，或是上一次使用文件大小更改操作（如 DBCC SHRINKFILE）设置的显式大小。 例如，如果数据库最初创建时的大小为 10 MB，后来增长到 100 MB，则该数据库最小只能收缩到 10 MB，即使已经删除数据库的所有数据也是如此。
  2. 被移动用来收缩文件的数据可以分布到文件的任何可用位置。 这将导致索引碎片并使搜索索引范围的查询变慢。 若要消除碎片，请考虑在收缩后重新生成文件的索引。
  3. 收缩任务要求具有 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员身份。
  4. 如果出现执行收缩而始终无法成功，建议尝试检查数据库健康状态。
1、常规修复
DBCC CHECKDB ('数据库名')
2、快速修复
DBCC CHECKDB ('数据库名', REPAIR_FAST)    
3、重建索引并修复
DBCC CHECKDB ('数据库名', REPAIR_REBUILD)
4、如果必要允许丢失数据修复
DBCC CHECKDB ('数据库名', REPAIR_ALLOW_DATA_LOSS)

# 建议是采用单用户模式
ALTER DATABASE 数据库名 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB(N'数据库名', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE 数据库名 SET MULTI_USER WITH ROLLBACK IMMEDIATE