SQL Server数据库收缩相关知识笔记_Sql Server

1、为什么要进行数据库收缩?

SQL Server 数据库采取预先分配空间的方法来建立数据库的数据文件或者日志文件，比如数据文件的空间分配了300MB，而实际上只占用了20MB空间，这样就会造成磁盘存储空间的浪费。可以通过数据库收缩技术对数据库中的每个文件进行收缩，删除已经分配但没有使用的页。从而节省服务器的存储的成本。

2、数据库收缩的原理

官方解释：收缩数据文件通过将数据页从文件末尾移动到更靠近文件开头的未占用的空间来恢复空间。在文件末尾创建足够的可用空间后，可以取消对文件末尾的数据页的分配并将它们返回给文件系统。

3、数据库收缩的限制和局限

收缩后的数据库不能小于数据库最初创建时指定的大小。或是上一次使用文件大小更改操作(如 DBCC SHRINKFILE)设置的显式大小。

比如：如果数据库最初创建时的大小为 10 MB，后来增长到 100 MB，则该数据库最小只能收缩到 10 MB，即使已经删除数据库的所有数据也是如此。

不能在备份数据库时收缩数据库。反之，也不能在数据库执行收缩操作时备份数据库。

4、数据库收缩的方式

4.1 收缩数据库 DBCC SHRINKDATABASE

介绍：收缩指定数据库中的数据文件大小。

语法格式：

DBCC SHRINKDATABASE

( database_name [ , target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

参数说明：

database_name：是要收缩的数据库名称
target_percent：是数据库收缩后的数据库文件中所要的剩余可用空间百分比。
NOTRUNCATE：导致在数据库文件中保留所释放的文件空间。如果未指定，将所释放的文件空间释放给操作系统。
TRUNCATEONLY：导致将数据文件中的任何未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小，从而减少文件大小，而不移动任何数据。不试图重新定位未分配页的行。使用 TRUNCATEONLY 时，忽略 target_percentis。

4.2 收缩数据库文件 DBCC SHRINKFILE

介绍：收缩当前数据库的指定数据或日志文件的大小，或通过将数据从指定的文件移动到相同文件组中的其他文件来清空文件，以允许从数据库中删除该文件。文件大小可以收缩到比创建该文件时所指定的大小更小。这样会将最小文件大小重置为新值。

语法格式：

DBCC SHRINKFILE

(

    { file_name | file_id }

    { [ , EMPTYFILE ]

    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]

    }

)

[ WITH NO_INFOMSGS ]

参数说明：

file_name：要收缩的文件的逻辑名称。
file_id：要收缩的文件的标识 (ID) 号。若要获得文件 ID，请使用 FILE_IDEX 系统函数，或查询当前数据库中的 sys.database_files 目录视图
target_size：用兆字节表示的文件大小(用整数表示)。如果未指定，则 DBCC SHRINKFILE 将文件大小减少到默认文件大小。默认大小为创建文件时指定的大小。
注意：可以使用 DBCC SHRINKFILE target_size 减小空文件的默认大小。
例如，如果创建一个10MB 的文件，然后在文件仍然为空的时候将文件收缩为2 MB，默认文件大小将设置为2 MB。这只适用于永远不会包含数据的空文件。
EMPTYFILE：将指定文件中的所有数据迁移到同一文件组中的其他文件。由于数据库引擎不再允许将数据放在空文件内，因此可以使用 ALTER DATABASE 语句来删除该文件。
NOTRUNCATE：在指定或不指定 target_percent 的情况下，将已分配的页从数据文件的末尾移动到该文件前面未分配页。文件末尾的可用空间不会返回给操作系统，文件的物理大小也不会更改。因此，指定 NOTRUNCATE 时，文件看起来未收缩。
NOTRUNCATE 只适用于数据文件。日志文件不受影响。
TRUNCATEONLY：将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。数据文件只收缩到最后分配的区。如果随 TRUNCATEONLY 指定了 target_size，则会忽略该参数。TRUNCATEONLY 只适用于数据文件。
WITH NO_INFOMSGS：取消显示所有信息性消息。

5、示例

-- 将TestDB数据库中的TestDB文件的大小收缩到20MB。

USE TestDB ;

GO

DBCC SHRINKFILE (TestDB, 20) ;

-- 将减小 UserDB 用户数据库中数据文件和日志文件的大小，

-- 以便在数据库中留出 10% 的可用空间

DBCC SHRINKDATABASE (TestDB, 30);

-- 清理数据库日志文件为2M

USE master

ALTER DATABASE TestDB SET RECOVERY SIMPLE WITH NO_WAIT

ALTER DATABASE TestDB SET RECOVERY SIMPLE --简单模式

USE TestDB

DBCC SHRINKFILE (N'TestDB_log' , 2, TRUNCATEONLY)

--设置压缩后的日志大小为2M，可以自行指定

USE master

ALTER DATABASE TestDB SET RECOVERY FULL WITH NO_WAIT

ALTER DATABASE TestDB SET RECOVERY FULL --还原为完全模式