DBCC SHRINKFILE (Transact-SQL)DBCC SHRINKFILE (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

收缩当前数据库的指定数据或日志文件大小。Shrinks the current database's specified data or log file size. 可以使用它将一个文件中的数据移到同一文件组中的其他文件,这会清空文件,从而允许删除数据库。You can use it to move data from one file to other files in the same filegroup, which empties the file and allows for its database removal. 可以将文件收缩到小于创建大小,同时将最小文件大小重置为新值。You can shrink a file to less than its size at creation, resetting the minimum file size to the new value.

文章链接图标 Transact-SQL 语法约定Article link icon Transact-SQL Syntax Conventions

语法Syntax

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

参数Arguments

file_namefile_name
要收缩的文件的逻辑名称。The file to be shrunk's logical name.

file_idfile_id
要收缩的文件的标识 (ID) 号。The file to be shrunk's identification (ID) number. 若要获取文件 ID,请使用 FILE_IDEX 系统函数,或查询当前数据库中的 sys.database_files 目录视图。To get a file ID, use the FILE_IDEX system function or query the sys.database_files catalog view in the current database.

target_sizetarget_size
整数,文件的新大小(以 MB 为单位)。An integer - the file's new megabyte size. 如果未指定,DBCC SHRINKFILE 缩小到文件创建大小。If not specified, DBCC SHRINKFILE reduces to the file creation size.

备注

可以使用 DBCC SHRINKFILE target_size 缩小空文件的默认大小。You can reduce an empty file's default size using DBCC SHRINKFILE target_size. 例如,如果创建一个 5 MB 的文件,然后在文件仍然为空的时候将文件收缩为 3 MB,默认文件大小将设置为 3 MB。For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. 这只适用于永远不会包含数据的空文件。This applies only to empty files that have never contained data.

FILESTREAM 文件组容器不支持此选项。This option isn't supported for FILESTREAM filegroup containers.
如果 target_size 已指定,DBCC SHRINKFILE 会尝试将文件收缩到目标大小。If specified, DBCC SHRINKFILE tries to shrink the file to target_size. 要释放的文件区域中的已用页移到文件保留区域中的可用空间。Used pages in the file's area to be freed are moved to free space in the file's kept areas. 例如,对于 10MB 数据文件,target_size 为 8 的 DBCC SHRINKFILE 操作会将文件最后 2MB 中的所有已用页移到文件前 8MB 中的任何未分配页区域中。For example, with a 10-MB data file, a DBCC SHRINKFILE operation with an 8 target_size moves all used pages in the file's last 2 MB into any unallocated pages in the file's first 8 MB. DBCC SHRINKFILE 不会收缩已超过所需存储数据大小的文件。DBCC SHRINKFILE doesn't shrink a file past the needed stored data size. 例如,如果使用 10 MB 数据文件中的 7 MB,则带有 target_size 为 6 的 DBCC SHRINKFILE 语句只能将该文件收缩到 7 MB,而不能收缩到 6 MB。For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

EMPTYFILEEMPTYFILE
将指定文件中的所有数据迁移到同一文件组中的其他文件。Migrates all data from the specified file to other files in the same filegroup. 也就是说,EMPTYFILE 将指定文件中的数据迁移到同一文件组中的其他文件。In other words, EMPTYFILE migrates data from a specified file to other files in the same filegroup. EMPTYFILE 确保不会将任何新数据添加到文件中(尽管此文件不是只读文件)。EMPTYFILE assures you that no new data gets added to the file, despite this file not being read-only. 可以使用 ALTER DATABASE 语句删除文件。You can use the ALTER DATABASE statement to remove a file. 如果你使用 ALTER DATABASE 语句更改文件大小,只读标志会重置,并能添加数据。If you use the ALTER DATABASE statement to change file size, the read-only flag is reset and data can be added.

对于 FILESTREAM 文件组容器,无法使用 ALTER DATABASE 删除文件,除非 FILESTREAM 垃圾回收器已运行,并删除了 EMPTYFILE 已复制到另一个容器的所有不必要文件组容器文件。For FILESTREAM filegroup containers, you can't use ALTER DATABASE to remove a file until the FILESTREAM Garbage Collector has run and deleted all the unnecessary filegroup container files that EMPTYFILE has copied to another container. 有关详细信息,请参阅 sp_filestream_force_garbage_collection (Transact-SQL)For more information, see sp_filestream_force_garbage_collection (Transact-SQL)

备注

有关删除 FILESTREAM 容器的信息,请参阅 ALTER DATABASE 文件和文件组选项 (Transact-SQL) 中的相应章节For information on removing a FILESTREAM container, see the corresponding section in ALTER DATABASE File and Filegroup Options (Transact-SQL)

NOTRUNCATENOTRUNCATE
无论是否指定 target_percent,将数据文件末尾中的已分配页移到文件开头的未分配页区域中。Moves allocated pages from a data file's end to unallocated pages in a file's front with or without specifying target_percent. 操作系统不会回收文件末尾的可用空间,文件的物理大小也不会改变。The free space at the file's end isn't returned to the operating system, and the file's physical size does not change. 因此,如果指定 NOTRUNCATE,文件看起来就像没有收缩一样。Therefore, if NOTRUNCATE is specified, the file appears not to shrink. NOTRUNCATE 只适用于数据文件。NOTRUNCATE is applicable only to data files. 日志文件不受影响。The log files are not affected. FILESTREAM 文件组容器不支持此选项。This option isn't supported for FILESTREAM filegroup containers.

TRUNCATEONLYTRUNCATEONLY
将文件末尾的所有可用空间释放给操作系统,但不在文件内部移动任何页。Releases all free space at the file's end to the operating system but does not perform any page movement inside the file. 数据文件只收缩到最后分配的区。The data file is shrunk only to the last allocated extent. 如果使用 TRUNCATEONLY 指定,则会忽略 target_size。target_size is ignored if specified with TRUNCATEONLY.
TRUNCATEONLY 选项不会移动日志中的信息,但会删除日志文件末尾的失效 VLF。The TRUNCATEONLY option does not move information in the log, but does remove inactive VLFs from the end of the log file. FILESTREAM 文件组容器不支持此选项。This option isn't supported for FILESTREAM filegroup containers.

WITH NO_INFOMSGSWITH NO_INFOMSGS
取消显示所有信息性消息。Suppresses all informational messages.

结果集Result sets

下表描述了结果集列。The following table describes result set columns.

列名Column name 描述Description
DbIdDbId 数据库引擎Database Engine试图收缩的文件的数据库标识号。Database identification number of the file the 数据库引擎Database Engine tried to shrink.
FileIdFileId 数据库引擎Database Engine试图收缩的文件的文件标识号。The file identification number of the file the 数据库引擎Database Engine tried to shrink.
CurrentSizeCurrentSize 文件当前占用的 8 KB 页数。Number of 8-KB pages the file currently occupies.
MinimumSizeMinimumSize 文件最低可以占用的 8 KB 页数。Number of 8-KB pages the file could occupy, at minimum. 此数字对应于文件的大小下限或最初创建大小。This number corresponds to the minimum size or originally created size of a file.
UsedPagesUsedPages 文件当前使用的 8 KB 页数。Number of 8-KB pages currently used by the file.
EstimatedPagesEstimatedPages 数据库引擎Database Engine估计文件能够收缩到的 8 KB 页数。Number of 8-KB pages that the 数据库引擎Database Engine estimates the file could be shrunk down to.

RemarksRemarks

DBCC SHRINKFILE 适用于当前数据库的文件。DBCC SHRINKFILE applies to the current database's files. 有关如何更改当前数据库的详细信息,请参阅 USE (Transact-SQL)For more information about how to change the current database, see USE (Transact-SQL).

可以随时停止执行 DBCC SHRINKFILE 操作,并保留任何已完成的工作。You can stop DBCC SHRINKFILE operations at any point and any completed work is preserved. 如果你使用 EMPTYFILE 参数并取消操作,文件不会被标记,以防添加其他数据。If you use the EMPTYFILE parameter and cancel the operation, the file isn't marked to prevent additional data from being added.

当 DBCC SHRINKFILE 操作失败时,则会引发错误。When a DBCC SHRINKFILE operation fails, an error is raised.

其他用户可以在文件收缩期间使用数据库,数据库不必处于单用户模式。Other users can work in the database during file shrinking - the database doesn't have to be in single-user mode. 无需在单用户模式下运行 SQL ServerSQL Server 实例,即可收缩系统数据库。You don't have to run the instance of SQL ServerSQL Server in single-user mode to shrink the system databases.

收缩日志文件Shrinking a log file

对于日志文件,数据库引擎Database Engine 使用 target_size 计算整个日志的目标大小。For log files, the 数据库引擎Database Engine uses target_size to calculate the whole log's target size. 因此,target_size 是执行收缩操作后的日志可用空间。Therefore, target_size is the log's free space after the shrink operation. 随后,整个日志的目标大小转换为每个日志文件的目标大小。The whole log's target size is then translated to each log file's target size. DBCC SHRINKFILE 尝试立即将每个物理日志文件收缩到其目标大小。DBCC SHRINKFILE tries to shrink each physical log file to its target size immediately. 但是,如果部分逻辑日志位于超出目标大小的虚拟日志中,则数据库引擎Database Engine将释放尽可能多的空间,并发出一条信息性消息。However, if part of the logical log resides in the virtual logs beyond the target size, the 数据库引擎Database Engine frees as much space as possible, and then issues an informational message. 该消息说明需要执行哪些操作来将逻辑日志移出位于文件末尾的虚拟日志。The message describes what actions are required to move the logical log out of the virtual logs at the end of the file. 执行这些操作以后,DBCC SHRINKFILE 可用于释放剩余空间。After the actions are performed, DBCC SHRINKFILE can be used to free the remaining space.

因为日志文件只能收缩到虚拟日志文件边界,所以可能无法将日志文件收缩到小于虚拟日志文件(即使没在使用它)。Because a log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it isn't being used. 数据库引擎Database Engine在日志文件创建或扩展时,动态选择虚拟日志文件大小。The 数据库引擎Database Engine dynamically chooses the virtual file log size when log files are created or extended.

最佳做法Best practices

在计划收缩文件时,请考虑以下信息:Consider the following information when you plan to shrink a file:

  • 在执行会产生大量未用空间的操作(如截断表或删除表操作)后,执行收缩操作最有效。A shrink operation is most effective after an operation that creates a large amount of unused space, such as a truncate table or a drop table operation.

  • 大多数数据库都需要一些可用空间,以供常规日常操作使用。Most databases require some available free space for regular day-to-day operations. 如果反复收缩数据库,并且它的大小再次增长,那么常规操作可能需要收缩空间。If you shrink a database repeatedly and its size grows again, then it's likely that regular operations require the shrunk space. 在这种情况下,反复收缩数据库是一种无谓的操作。In these cases, repeatedly shrinking the database is a wasted operation.

  • 收缩操作不保留数据库中索引的碎片状态,通常还会在一定程度上增加碎片。A shrink operation doesn't preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. 此类碎片是不要反复收缩数据库的另一个原因。This fragmentation is another reason not to repeatedly shrink the database.

  • 按顺序而非同时缩小同一数据库中的多个文件。Shrink multiple files in the same database sequentially instead of concurrently. 对系统表的争用可能会导致阻塞,进而导致延迟。Contention on system tables can cause blocking and lead to delays.

故障排除Troubleshooting

本部分介绍如何诊断和更正在运行 DBCC SHRINKFILE 命令时可能发生的问题。This section describes how to diagnose and correct issues that can occur when running the DBCC SHRINKFILE command.

文件不收缩The file doesn't shrink

如果在执行无错误收缩操作后文件大小未改变,请尝试执行以下操作,验证文件是否有足够的可用空间:If the file size doesn't change after an error-less shrink operation, try the following to verify that the file has adequate free space:

  • 运行以下查询。Run the following query.
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
  • 运行 DBCC SQLPERF 命令以返回事务日志中使用的空间。Run the DBCC SQLPERF command to return the space used in the transaction log.

如果可用空间不足,收缩操作无法进一步缩小文件大小。The shrink operation can't reduce the file size any further if there's insufficient free space available.

通常情况下,似乎不收缩的是日志文件。Typically it's the log file that appears not to shrink. 导致这种不收缩的原因通常是,日志文件尚未截断。This non-shrinking is usually the result of a log file that hasn't been truncated. 若要截断日志,可以将数据库恢复模式设置为 SIMPLE,或者先备份日志,再重新运行 DBCC SHRINKFILE 操作。To truncate the log, you can set the database recovery model to SIMPLE, or back up the log and then run the DBCC SHRINKFILE operation again.

收缩操作受阻The shrink operation is blocked

基于行版本控制的隔离级别下运行的事务可能会阻止收缩操作。A transaction running under a row versioning-based isolation level can block shrink operations. 例如,如果在执行 DBCC SHRINK DATABASE 操作时,正在基于行版本控制的隔离级别下运行大型删除操作,那么收缩操作会等到删除操作完成,然后才会继续。For example, if a large delete operation running under a row versioning-based isolation level is in progress when a DBCC SHRINK DATABASE operation executes, the shrink operation waits for the delete to complete before continuing. 出现这种阻塞时,DBCC SHRINKFILE 和 DBCC SHRINKDATABASE 操作会将信息性消息(对于 SHRINKDATABASE 为 5202,对于 SHRINKFILE 为 5203)打印输出到 SQL Server 错误日志。When this blocking happens, DBCC SHRINKFILE and DBCC SHRINKDATABASE operations print out an informational message (5202 for SHRINKDATABASE and 5203 for SHRINKFILE) to the SQL Server error log. 在第一个小时内,此消息每五分钟记录一次,之后每一小时记录一次。This message is logged every five minutes in the first hour and then every hour. 例如,如果错误日志包含以下错误消息,则会发生以下错误:For example, if the error log contains the following error message then the following error will occur:

DBCC SHRINKFILE for file ID 1 is waiting for the snapshot   
transaction with timestamp 15 and other snapshot transactions linked to   
timestamp 15 or with timestamps older than 109 to finish.  

此消息指明,时间戳早于 109(收缩操作完成的最后一个事务)的快照事务正在阻止收缩操作。This message means snapshot transactions with timestamps older than 109 (the last transaction that the shrink operation completed) are blocking the shrink operation. 它还指明,sys.dm_tran_active_snapshot_database_transactions 动态管理视图中的 transaction_sequence_num 或 first_snapshot_sequence_num 列包含值 15。It also indicates the transaction_sequence_num, or first_snapshot_sequence_num columns in the sys.dm_tran_active_snapshot_database_transactions dynamic management view contains a value of 15. 如果 transaction_sequence_num 或 first_snapshot_sequence_num 视图列包含的数字小于收缩操作完成的最后一个事务 (109),收缩操作会等待这些事务完成。If either the transaction_sequence_num or first_snapshot_sequence_num view column contains a number less than a shrink operation's last completed transaction (109), the shrink operation waits for those transactions to finish.

若要解决此问题,请执行下列任务之一:To resolve the issue, you can do one of the following tasks:

  • 终止阻止收缩操作的事务。End the transaction that is blocking the shrink operation.
  • 终止收缩操作。End the shrink operation. 如果收缩操作终止,所有已完成的工作都会保留。Any completed work is kept if the shrink operation ends.
  • 不执行任何操作,并允许收缩操作等到阻塞事务完成。Do nothing and allow the shrink operation to wait until the blocking transaction completes.

权限Permissions

要求具有 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员身份。Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

示例Examples

将数据文件收缩到指定的目标大小Shrinking a data file to a specified target size

以下示例将 UserDB 用户数据库中名为 DataFile1 的数据文件的大小收缩到 7 MB。The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.

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

将日志文件收缩到指定的目标大小Shrinking a log file to a specified target size

以下示例将 AdventureWorks 数据库中的日志文件收缩到 1 MB。The following example shrinks the log file in the AdventureWorks database to 1 MB. 若要允许 DBCC SHRINKFILE 命令收缩文件,首先需要通过将数据库恢复模式设置为 SIMPLE 来截断该文件。To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

USE AdventureWorks2012;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  
GO  
-- Reset the database recovery model.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY FULL;  
GO  

C.C. 截断数据文件Truncating a data file

下面的示例将截断 AdventureWorks 数据库中的主数据文件。The following example truncates the primary data file in the AdventureWorks database. 需要查询 sys.database_files 目录视图以获得数据文件的 file_idThe sys.database_files catalog view is queried to obtain the file_id of the data file.

USE AdventureWorks2012;  
GO  
SELECT file_id, name  
FROM sys.database_files;  
GO  
DBCC SHRINKFILE (1, TRUNCATEONLY);  

D.D. 清空文件Emptying a file

下面的示例展示了如何清空文件,这样文件就能从数据库中删除。The following example demonstrates emptying a file so it can be removed from the database. 为了方便此示例进行展示,先创建包含数据的数据文件。For this example's purposes, a data file is first created and contains data.

USE AdventureWorks2012;  
GO  
-- Create a data file and assume it contains data.  
ALTER DATABASE AdventureWorks2012   
ADD FILE (  
    NAME = Test1data,  
    FILENAME = 'C:\t1data.ndf',  
    SIZE = 5MB  
    );  
GO  
-- Empty the data file.  
DBCC SHRINKFILE (Test1data, EMPTYFILE);  
GO  
-- Remove the data file from the database.  
ALTER DATABASE AdventureWorks2012  
REMOVE FILE Test1data;  
GO  

另请参阅See Also

ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL)
DBCC (Transact-SQL)DBCC (Transact-SQL)
DBCC SHRINKDATABASE (Transact-SQL)DBCC SHRINKDATABASE (Transact-SQL)
FILE_ID (Transact-SQL)FILE_ID (Transact-SQL)
sys.database_files (Transact-SQL)sys.database_files (Transact-SQL)
收缩文件Shrink a File