DBCC SHRINKDATABASE (Transact-SQL)DBCC SHRINKDATABASE (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 size of the data and log files in the specified database.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { NOTRUNCATE | TRUNCATEONLY } ]   
)  
[ WITH NO_INFOMSGS ]  

参数Arguments

database_name | database_id | 0database_name | database_id | 0
要收缩的数据库名称或 ID。Is the database name or ID to be shrunk. 0 指定使用当前数据库。0 specifies that the current database is used.

target_percenttarget_percent
数据库收缩后的数据库文件中所需的剩余可用空间百分比。Is the percentage of free space that you want left in the database file after the database has been shrunk.

NOTRUNCATENOTRUNCATE
将分配的页面从文件的末尾移动到文件前面的未分配页面。Moves assigned pages from the file's end to unassigned pages in the front of the file. 此操作会压缩文件中的数据。This action compacts the data within the file. target_percent 是可选的。target_percent is optional. Azure SQL 数据仓库不支持此选项。Azure SQL Data Warehouse doesn't support this option.

文件末尾的可用空间不会返回给操作系统,并且文件的物理大小也不会更改。The free space at the end of the file isn't returned to the operating system, and the physical size of the file doesn't change. 因此,指定 NOTRUNCATE 时,数据库似乎不会收缩。As such, the database appears not to shrink when you specify NOTRUNCATE.

NOTRUNCATE 只适用于数据文件。NOTRUNCATE is applicable only to data files. NOTRUNCATE 不影响日志文件。NOTRUNCATE doesn't affect the log file.

TRUNCATEONLYTRUNCATEONLY
将文件末尾的所有可用空间释放给操作系统。Releases all free space at the end of the file to the operating system. 不移动文件内的任何页面。Doesn't move any pages inside the file. 数据文件仅收缩到最后指定的盘区。The data file shrinks only to the last assigned extent. 如果使用 TRUNCATEONLY 指定,则会忽略 target_percentIgnores target_percent if specified with TRUNCATEONLY. Azure SQL 数据仓库不支持此选项。Azure SQL Data Warehouse doesn't support this option.

TRUNCATEONLY 将影响日志文件。TRUNCATEONLY affects the log file. 若要仅截断数据文件,请使用 DBCC SHRINKFILE。To truncate only the data file, use DBCC SHRINKFILE.

WITH NO_INFOMSGSWITH NO_INFOMSGS
取消严重级别从 0 到 10 的所有信息性消息。Suppresses all informational messages that have severity levels from 0 through 10.

结果集Result Sets

下表对结果集中的列进行了说明。The following table describes the columns in the result set.

列名Column name 描述Description
DbIdDbId 数据库引擎Database Engine试图收缩的文件的数据库标识号。Database identification number of the file the 数据库引擎Database Engine tried to shrink.
FileIdFileId 数据库引擎Database Engine尝试收缩的文件的文件标识号。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 value 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.

备注

数据库引擎Database Engine不显示未收缩的文件的行。The 数据库引擎Database Engine does not display rows for those files not shrunk.

RemarksRemarks

备注

当前,Azure SQL 数据仓库不支持 DBCC SHRINKDATABASE。Currently Azure SQL Data Warehouse does not support DBCC SHRINKDATABASE. 不建议运行此命令,因为这是 I/O 密集型操作,可能会使数据仓库离线。Running this command is not recommended as this is an i/o intensive operation and can take your data warehouse offline. 此外,运行此命令后,还会对数据仓库快照产生成本影响。In addition, there will be costing implications to your data warehouse snapshots after running this command.

若要收缩特定数据库的所有数据和日志文件,请执行 DBCC SHRINKDATABASE 命令。To shrink all data and log files for a specific database, execute the DBCC SHRINKDATABASE command. 若要一次收缩一个特定数据库中的一个数据或日志文件,请执行 DBCC SHRINKFILE 命令。To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command.

若要查看数据库中当前的可用(未分配)空间量,请运行 sp_spaceusedTo view the current amount of free (unallocated) space in the database, run sp_spaceused.

可在进程中的任一点停止 DBCC SHRINKDATABASE 操作,任何已完成的工作都将保留。DBCC SHRINKDATABASE operations can be stopped at any point in the process, and any completed work is kept.

数据库不能小于配置的数据库最小大小。The database can't be smaller than the configured minimum size of the database. 在最初创建数据库时指定最小大小。You specify the minimum size when the database is originally created. 或者,最小大小可以是使用文件大小更改操作显式设置的最后大小。Or, the minimum size can be the last size explicitly set by using a file size changing operation. DBCC SHRINKFILE 或 ALTER DATABASE 等操作是文件大小更改操作的示例。Operations like DBCC SHRINKFILE or ALTER DATABASE are examples of file-size changing operations.

假设最初创建的数据库大小为 10 MB。Let's say a database is originally created with a size of 10 MB in size. 然后,它增长到 100 MB。Then, it grows to 100 MB. 即使数据库中的所有数据都已删除,数据库可以减少到的最小大小也为 10 MB。The smallest the database can be reduced to is 10 MB, even if all the data in the database has been deleted.

运行 DBCC SHRINKDATABASE 时,请指定 NOTRUNCATE 选项或 TRUNCATEONLY 选项。Specify either the NOTRUNCATE option or the TRUNCATEONLY option when you run DBCC SHRINKDATABASE. 如果不这样做,结果与使用 NOTRUNCATE 运行 DBCC SHRINKDATABASE 操作,然后再使用 TRUNCATEONLY 运行 DBCC SHRINKDATABASE 操作相同。If you don't, the result is the same as if you run a DBCC SHRINKDATABASE operation with NOTRUNCATE followed by running a DBCC SHRINKDATABASE operation with TRUNCATEONLY.

收缩数据库不必处于单用户模式。The shrunk database doesn't have to be in single user mode. 其他用户可以在数据库收缩时在其中工作,包括系统数据库。Other users can be working in the database when it's shrunk, including system databases.

备份数据库时,无法收缩数据库。You can't shrink a database while the database is being backed up. 反之,也不能在数据库执行收缩操作时备份数据库。Conversely, you can't back up a database while a shrink operation on the database is in process.

DBCC SHRINKDATABASE 的工作原理How DBCC SHRINKDATABASE Works

DBCC SHRINKDATABASE 以每个文件为单位对数据文件进行收缩。然而,DBCC SHRINKDATABASE 在对日志文件进行收缩时,它将视为所有的日志文件都存在于一个连续的日志池中。DBCC SHRINKDATABASE shrinks data files on a per-file basis, but shrinks log files as if all the log files existed in one contiguous log pool. 文件始终从末尾开始收缩。Files are always shrunk from the end.

假设拥有几个日志文件、一个数据文件和一个名为 mydb 的数据库。Assume you have a couple of log files, a data file, and a database named mydb. 数据文件和日志文件分别是 10 MB,并且数据文件包含 6 MB 数据。The data and log files are 10 MB each and the data file contains 6 MB of data. 数据库引擎Database Engine 计算每个文件的目标大小。The 数据库引擎Database Engine calculates a target size for each file. 此值是文件要收缩到的大小。This value is the size to which the file is to be shrunk. 如果使用 target_percent 指定 DBCC SHRINKDATABASE ,则 数据库引擎Database Engine 计算得出的目标大小为收缩后文件中可用空间的 target_percent 数量。When DBCC SHRINKDATABASE is specified with target_percent, the 数据库引擎Database Engine calculates target size to be the target_percent amount of space free in the file after shrinking.

例如,如果为收缩 mydbtarget_percent 指定为 25,则 数据库引擎Database Engine 计算得出此文件的目标大小为 8 MB(6 MB 数据加上 2 MB 可用空间)。For example, if you specify a target_percent of 25 for shrinking mydb, the 数据库引擎Database Engine calculates the target size for the data file to be 8 MB (6 MB of data plus 2 MB of free space). 因此,数据库引擎Database Engine 将数据文件后 2 MB 中的所有数据移动到数据文件前 8 MB 的任何可用空间中,然后对该文件进行收缩。As such, the 数据库引擎Database Engine moves any data from the data file's last 2 MB to any free space in the data file's first 8 MB and then shrinks the file.

假设 mydb 的数据文件包含 7 MB 的数据 。Assume the data file of mydb contains 7 MB of data. target_percent 指定为 30,以允许将此数据文件收缩到可用空间的 30%。Specifying a target_percent of 30 allows for this data file to be shrunk to the free percentage of 30. 但是,将 target_percent 指定为 40 不会收缩数据文件,因为 数据库引擎Database Engine 将文件收缩到的大小不能小于数据当前占用的空间大小。However, specifying a target_percent of 40 doesn't shrink the data file because the 数据库引擎Database Engine won't shrink a file to a size smaller than the data currently occupies.

还可以用另一种方法来思考此问题:40% 的所要求可用空间加上 70% 的整个数据文件大小(10 MB 中的 7 MB)超过了 100%。You can also think of this issue another way: 40 percent wanted free space + 70 percent full data file (7 MB out of 10 MB) is more than 100 percent. 任何大于 30 的 target_size 都不会收缩数据文件。Any target_size greater than 30 won't shrink the data file. 它不会收缩是因为所需的可用百分比加上数据文件当前占用的百分比大于 100%。It won't shrink because the percentage free you want plus the current percentage that the data file occupies is over 100 percent.

对于日志文件,数据库引擎Database Engine 使用 target_percent 计算整个日志的目标大小。For log files, the 数据库引擎Database Engine uses target_percent to calculate the target size for the whole log. 这就是为什么 target_percent 是收缩操作后日志中的可用空间量的原因。That's why target_percent is the amount of free space in the log after the shrink operation. 之后,整个日志的目标大小转换为每个日志文件的目标大小。Target size for the whole log is then translated to a target size for each log file.

DBCC SHRINKDATABASE 尝试立即将每个物理日志文件收缩到其目标大小。DBCC SHRINKDATABASE tries to shrink each physical log file to its target size immediately. 假设逻辑日志没有任何部分位于超出日志文件目标大小的虚拟日志中。Let's say no part of the logical log stays in the virtual logs beyond the target size of the log file. 然后文件成功截断,DBCC SHRINKDATABASE 完成且没有生成任何消息。Then the file is successfully truncated and DBCC SHRINKDATABASE finishes without any messages. 但是,如果部分逻辑日志位于超出目标大小的虚拟日志中,则 数据库引擎Database Engine 将释放尽可能多的空间,并发出一条信息性消息。However, if part of the logical log stays 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 SHRINKDATABASE 可用于释放剩余空间。After the actions are run, DBCC SHRINKDATABASE can be used to free the remaining space.

日志文件只能收缩到虚拟日志文件边界。A log file can only be shrunk to a virtual log file boundary. 这就是为什么不可能将日志文件收缩到小于虚拟日志文件大小的原因。That's why shrinking a log file to a size smaller than the size of a virtual log file might not be possible. 即使未在使用它也可能无法实现。It might not be possible even if it isn't being used. 虚拟日志文件的大小在创建或扩展这些日志文件时由数据库引擎Database Engine动态选择。The size of the virtual log file is chosen dynamically by the 数据库引擎Database Engine when log files are created or extended.

最佳实践Best Practices

当您计划收缩数据库时,请考虑以下信息:Consider the following information when you plan to shrink a database:

  • 收缩操作在执行某个操作后最有效。A shrink operation is most effective after an operation. 此操作会创建未使用的空间,例如截断表或删除表操作。This operation creates unused space, such as a truncate table or a drop table operation.
  • 大多数数据库都需要一些可用空间,以供常规日常操作使用。Most databases require some free space to be available for regular day-to-day operations. 可能会反复收缩数据库,并注意到数据库大小再次增长。You might shrink a database repeatedly and notice that the database size grows again. 这种增长表明常规操作需要使用收缩的空间。This growth indicates that the shrunken space is required for regular operations. 在这种情况下,反复收缩数据库是一种无谓的操作。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 result is another reason not to repeatedly shrink the database.
  • 除非有特定要求,否则不要将 AUTO_SHRINK 数据库选项设置为 ON。Unless you have a specific requirement, don't set the AUTO_SHRINK database option to ON.

故障排除Troubleshooting

收缩操作可能会被在基于行版本控制的隔离级别下运行的事务阻止。It's possible to block shrink operations by a transaction that is running under a row versioning-based isolation level. 例如,在执行 DBCC SHRINK DATABASE 操作时,正在基于行版本控制的隔离级别下运行大型删除操作。For example, a large delete operation running under a row versioning-based isolation level is in progress when a DBCC SHRINK DATABASE operation is executed. 当这种情况发生时,收缩操作会等到删除操作完成后再收缩文件。When this situation happens, the shrink operation will wait for the delete operation to complete before it shrinks the files. 收缩操作在等待时,DBCC SHRINKFILE 和 DBCC SHRINKDATABASE 操作会打印输出信息性消息(对于 SHRINKDATABASE 为 5202,对于 SHRINKFILE 为 5203)。When the shrink operation waits, DBCC SHRINKFILE and DBCC SHRINKDATABASE operations print out an informational message (5202 for SHRINKDATABASE and 5203 for SHRINKFILE). 此消息在第一个小时内每五分钟打印到 SQL ServerSQL Server 错误日志一次,然后在后续每个小时打印一次。This message prints to the SQL ServerSQL Server error log every five minutes in the first hour and then every upcoming hour. 例如,如果错误日志包含以下错误消息:For example, if the error log contains the following error message:

DBCC SHRINKDATABASE for database ID 9 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 error means snapshot transactions that have timestamps older than 109 will block the shrink operation. 该事务是收缩操作完成的最后一个事务。That transaction is the last transaction that the shrink operation completed. 它还说明 sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) 动态管理视图中的 transaction_sequence_numfirst_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 (Transact-SQL) dynamic management view contain a value of 15. 该视图中的 transaction_sequence_numfirst_snapshot_sequence_num 列可能包含小于收缩操作完成的最后一个事务 (109) 的数字。The transaction_sequence_num or first_snapshot_sequence_num column in the view might contain a number that is less than the last transaction completed by a shrink operation (109). 如果是这样,收缩操作将等待这些事务完成。If so, the shrink operation will wait for those transactions to finish.

若要解决此问题,请执行下列任务之一:To resolve the problem, 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.
  • 不执行任何操作,并允许收缩操作等到阻塞事务完成。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

A.A. 收缩数据库并指定可用空间的百分比Shrinking a database and specifying a percentage of free space

以下示例将减小 UserDB 用户数据库中数据文件和日志文件的大小,以便在数据库中留出 10% 的可用空间。The following example reduces the size of the data and log files in the UserDB user database to allow for 10 percent free space in the database.

DBCC SHRINKDATABASE (UserDB, 10);  
GO  

B.B. 截断数据库Truncating a database

以下示例将 AdventureWorks 示例数据库中的数据和日志文件收缩到最后指定的盘区。The following example shrinks the data and log files in the AdventureWorks sample database to the last assigned extent.

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);  

另请参阅See also

ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL)
DBCC (Transact-SQL)DBCC (Transact-SQL)
DBCC SHRINKFILE (Transact-SQL)DBCC SHRINKFILE (Transact-SQL)
收缩数据库Shrink a Database