DBCC SHRINKFILE收缩日志/收缩数据库/收缩文件

转自：https://www.cnblogs.com/gered/p/9366256.html#_label3

目录

• 【基本语法】
• 【注意事项】
  • （1）要切换到被收缩文件所在数据库,use db
  • （2）收缩时不用单用户
  • （3）收缩日志文件注意事项
  • （4）收缩结果集中列
• 【收缩数据文件、日志文件的影响】
  • （1）收缩数据文件的影响
  • （2）收缩日志文件的影响
• 【最佳实践步骤-收缩数据文件】
• 【最佳实践步骤-收缩日志文件】

【基本语法】

DBCC SHRINKFILE

收缩相关数据库的指定数据文件或日志文件大小。

语法

DBCC SHRINKFILE
    ( { file_name | file_id }
        { [ ,target_size ]
            | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ] 
        }
    )

参数

file_name： 是已收缩文件的逻辑名称。文件名必须符合标识符的规则。有关更多信息，请参见使用标识符。

file_id：　　是要收缩的文件的标识 (ID) 号。若要获得文件 ID，请使用 FILE_ID 函数或在当前数据库中搜索 sysfiles。

target_size：是用兆字节表示的所要的文件大小（用整数表示）。如果没有指定，DBCC SHRINKFILE 将文件大小减少到默认文件大小。

如果指定 target_size，DBCC SHRINKFILE 将试图将文件收缩到指定大小。将要释放的文件部分中的已使用页将重新定位到保留的文件部分中的可用空间。

　　例如，如果数据文件为 10MB，则带有 target_size 为 8 的 DBCC SHRINKFILE 将导致文件最后 2 MB 中所有已用页重新分配到文件前 8 MB 中的任何可用槽中。

　　DBCC SHRINKFILE 不会将文件收缩到小于存储文件中的数据所需要的大小。例如，如果使用 10MB 数据文件中的7 MB，带有 target_size 为 6 的 DBCC SHRINKFILE 语句只能将该文件收缩到 7 MB，而不能收缩到 6 MB。

EMPTYFILE：将所有数据从指定文件中迁移到同一文件组中的其它文件。Microsoft® SQL Server™ 不再允许将数据放在用于 EMPTYFILE 选项的文件上。该选项允许使用 ALTER DATABASE 语句除去文件。

NOTRUNCATE ：导致将释放的文件空间保留在文件中。

　　当与 target_size 一起指定 NOTRUNCATE 时，释放的空间不会释放给操作系统。

　　DBCC SHRINKFILE 的唯一影响是将已使用的页从 target_size 行上面重新定位到文件的前面。当未指定 NOTRUNCATE 时，所有释放的文件空间返回给操作系统。

TRUNCATEONLY：导致文件中的任何未使用的空间释放给操作系统，并将文件收缩到上一次分配的大小，从而减少文件大小，而不移动任何数据。不尝试将行重新定位到未分配页。如果使用 TRUNCATEONLY，将忽略 target_size。

【注意事项】

（1）要切换到被收缩文件所在数据库,use db

DBCC SHRINKFILE 适用于当前数据库中的文件。

将上下文切换到数据库，以发出引用该特定数据库中文件的 DBCC SHRINKFILE 语句。有关更改当前数据库的更多信息，请参见 USE。

所生成的数据库不能比 model 数据库更小。

使用 DBCC SHRINKFILE 以将文件减小到比其最初创建时更小。之后，文件的最小大小重置为新指定的大小。

若要删除在文件中可能出现的任何数据，请在执行ALTER DATABASE 之前执行 DBCC SHRINKFILE('file_name', EMPTYFILE)。

（2）收缩时不用单用户

要收缩的数据库不必在单用户模式下；收缩文件时，同时其他用户可以在数据库中工作。不必在单用户模式下运行 SQL Server 以对系统数据库进行收缩。

（3）收缩日志文件注意事项

对于日志文件，SQL Server 使用 target_size 以计算整个日志的目标大小；因此，target_size 是收缩操作完成后日志中的可用空间大小。

之后，整个日志的目标大小可以解释为每个日志文件的目标大小。DBCC SHRINKFILE 尝试立即将每个物理日志文件收缩至其目标大小。

如果虚拟日志中的所有逻辑日志部分都没有超出日志文件的目标大小，该文件将成功截断，DBCC SHRINKFILE 完成且不显示任何消息。

然而，如果虚拟日志中的逻辑日志部分超出目标大小，则 SQL Server 释放尽可能多的空间并发出一条消息。

该信息告诉您需要执行什么操作来移动文件末尾超出虚拟日志的逻辑日志部分。

执行完该操作后，可以重新发出 DBCC SHRINKFILE 命令以释放剩余的空间。有关收缩事务日志的更多信息，请参见收缩事务日志。

因为日志文件只能收缩到虚拟日志文件边界，所以不可能将日志文件收缩到比虚拟日志文件更小（即使现在没有使用该文件）。

例如，可以将数据库的 1 GB 日志文件收缩到只有 128 MB。有关截断的更多信息，请参见截断事务日志。有关确定虚拟日志文件大小的更多信息，请参见虚拟日志文件。

（4）收缩结果集中列

下表描述结果集内的列。

DbId：SQL Server 试图收缩的文件的数据库标识号。FileIdSQL Server 试图收缩的文件的文件标识号。

　　　　CurrentSize文件当前占用的 8KB 页数。MinimumSize文件可以占用的最小 8KB 页数。这与文件的最小大小或最初创建时的大小相对应。UsedPages文件当前使用的 8KB 页数。EstimatedPagesSQL Server 估计文件能够收缩到的 8KB 页数。

权限：DBCC SHRINKFILE 权限默认授予 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员且不可转让。

示例：

下例将 UserDB 用户数据库中名为 DataFil1 的文件收缩到 7 MB。这个名称是逻辑名称

USE UserDB
GO

DBCC SHRINKFILE (DataFil1, 7)
GO

【收缩数据文件、日志文件的影响】

（1）收缩数据文件的影响

数据库使用数据文件（扩展名是mdf 或 ndf)来存储数据，使用日志文件（扩展名是ldf）来存储事务日志，通常情况下，数据文件会持续增长，不会自动释放空闲空间，这样会导致硬盘空间耗尽。

如果一个数据库的文件有很多空闲空间，收缩数据库文件是一种解决硬盘空间紧张的直接方式。

原理与影响：

　　在SQL Server中，我们可以使用 DBCC ShrinkFile命令收缩数据文件，该命令首先将文件尾部的区（extent）移动到文件的开头，文件结尾的空闲的硬盘空间被释放给操作系统。

　　这种操作就像截断将文件的尾部一样，这种方式不需要消耗很多IO就能释放空间；

　　但是，如果空闲部分不在文件末尾时，收缩操作必须扫描数据文件，并对正在读取的页面加锁，把文件末尾的区移动到文件开头，这是一个IO密集型的操作，影响数据库的性能；

　　1、收缩操作不是一个独占行为，在做文件收缩时，其他用户仍然可以对数据库进行读写操作。

　　2、收缩会锁表，会阻塞

　　3、在任意一个时间点停止dbcc shrinkfile命令，任何已经完成的工作都将保留。

建议：

　　收缩 数据库 是非常耗费server性能的操作，如果没有必要不要收缩

　　收缩 无非就是把已经分配给数据库文件空间收回来，但是，收缩的时候 要移动数据页，而且可能造成大量的碎片，影响性能。

　　日志收缩和数据文件收缩不一样，日志中的虚拟文件状态 只有 在可复用 时候  才能收缩。

　　而且 凡是有活动的日志的日志虚拟文件都是活动的不能收缩。

（2）收缩日志文件的影响

日志文件收缩， 回收磁盘空间。
　　数据文件 根据设计  设计的大小，一般情况不用收缩，收缩可能带来性能的问题。

日志
　　如果 选择的日志恢复模型是完全，如果没有日志截断，日志增长的很大，
建议
　　备份日志。
　　　　backup log db to disk='备份设备'
　　截断日志
　　　　backup log db with no_log
　　然后收缩。
　　　　dbcc shrinkfile(2,10)

  原理参考：https://www.cnblogs.com/gallen-n/archive/2017/03/15/6555283.html

【最佳实践步骤-收缩数据文件】

1、实际工作中收缩数据文件的情况比收缩库的情况多，不建议直接收缩数据库

2、收缩很容易出现等待，收缩会话对应 sys.sysprocesses 的字段 waitresource 值类似为15:1:4700649，sys.sysprocesses 的字段  lastwaittype 值为PAGEIOLATCH_SH或PAGEIOLATCH_EX等

3、收缩数据文件时，不要一次性全部收缩。 可以每次收缩5G左右，比如DataFile1有32G，则每次收缩如下

USE UserDB;
DBCC SHRINKFILE (DataFile1, 27000);
GO

DBCC SHRINKFILE (DataFile1, 22000);
GO

4、数据文件的可用空间可以结合sys.master_files 和 FILEPROPERTY(name,'SpaceUsed') 来查看

5、收缩的100%进度可以通过 sys.dm_exec_requests 的字段 percent_complete 来看

6、收缩完后，记得重建索引

alter index all on table_name rebuild ;  -- with(online=on) 这个参数加了不影响线上

【最佳实践步骤-收缩日志文件】

ALTER DATABASE [test] SET RECOVERY SIMPLE WITH NO_WAIT
GO

EXEC('
USE [Db_Tank]
DBCC SHRINKFILE (''test_Log'' , 2048)  --sql server默认文件单位为M，所以这里是2G
')

print '【'+CONVERT(varchar(20),GETDATE(),120)+'】恢复库模式'
GO