收缩 tempdb 数据库

1、本文内容

注解
使用 ALTER DATABASE 命令
使用 DBCC SHRINKDATABASE 命令
使用 DBCC SHRINKFILE 命令
运行收缩操作时出现错误 8909

适用于：

SQL Server
Azure SQL 托管实例

本文讨论可用于收缩 SQL Server 中 tempdb 数据库的各种方法。

可以使用下列任一方法来更改 tempdb 大小。本文介绍了前三个选项。如果要使用 SQL Server Management Studio (SSMS)，请按照收缩数据库中的说明进行操作。

方法	需要重启？	详细信息
ALTER DATABASE	是	提供对默认 tempdb 文件（tempdev 和 templog）大小的完整控制。
DBCC SHRINKDATABASE	否	在数据库级别运行。
DBCC SHRINKFILE	否	允许收缩单个文件。
SQL Server Management Studio	否	通过图形用户界面收缩数据库文件。

2、注解

默认情况下，tempdb 数据库配置为根据需要自动增长。因此，此数据库可能会意外地增长到大于所需大小的大小。较大 tempdb 数据库大小不会对 SQL Server 的性能产生不利影响。

SQL Server 启动时，tempdb 使用数据库的副本重新创建 model，而 tempdb 重置为其上次配置的大小。配置的大小是使用文件大小更改操作（例如使用 MODIFY FILE 选项的 ALTER DATABASE，或者 DBCC SHRINKFILE 或 DBCC SHRINKDATABASE 语句）设置的最后一个显式大小。因此，除非必须使用不同的值或立即处理大型 tempdb 数据库，否则可以等待 SQL Server 服务的下一次重启以缩减大小。

tempdb 活动正在进行时，可以收缩 tempdb。但是，可能会遇到其他错误，例如阻止、死锁等，从而阻碍收缩完成。因此，为了确保成功收缩 tempdb，我们建议在服务器处于单用户模式或停止所有 tempdb 活动时执行此操作。

SQL Server 只记录 tempdb 事务日志中足够的信息来回滚事务，但在数据库恢复期间不会重做事务。此功能提高了 INSERT 中 tempdb 语句的性能。此外，无需记录信息以重做任何事务，因为 tempdb 每次重新启动 SQL Server 时都会重新创建。因此，它没有要前滚或回滚的事务。

3、使用 ALTER DATABASE 命令

此命令仅对默认 tempdb 逻辑文件 tempdev 和 templog 执行。 如果更多文件添加到 tempdb 中，则可以在重启 SQL Server 即服务后将其收缩。 所有 tempdb 文件都在启动期间重新创建。 但是，它们为空，可以删除。 如果要删除 tempdb 中其他文件，则使用包含 REMOVE FILE 选项的 ALTER DATABASE 命令。

此方法要求重启 SQL Server。

停止 SQL Server。

在命令提示符下，以最低配置模式启动实例。为此，请按照下列步骤进行操作：

a. 在命令提示符处，切换到安装 SQL Server 的文件夹（替换以下示例中的和）：

cd C:\Program Files\Microsoft SQL Server\MSSQL<VersionNumber>.<InstanceName>\MSSQL\Binn

b. 如果实例是 SQL Server 的命名实例，请运行以下命令（替换以下示例中的）：

sqlservr.exe -s <InstanceName> -c -f -mSQLCMD

c. 如果实例是 SQL Server 的默认实例，请运行以下命令：

sqlservr -c -f -mSQLCMD

 备注参数 -c 和 -f 会导致 SQL Server 以最小配置模式启动，tempdb 数据文件的大小为 1 MB，日志文件的大小为 0.5 MB。 参数 -mSQLCMD 可防止 sqlcmd 以外的任何其他应用程序接管单用户连接。

使用 sqlcmd 连接到 SQL Server 实例，然后运行以下 Transact-SQL 命令。替换 <target_size_in_MB> 为所需的大小：

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = <target_size_in_MB>);ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = <target_size_in_MB>);

停止 SQL Server。为此，请按命令提示符窗口的 Ctrl+C，重启 SQL Server 即服务，然后验证 tempdb.mdf 和 templog.ldf 文件的大小。

4、使用 DBCC SHRINKDATABASE 命令

DBCC SHRINKDATABASE 接收参数 target_percent。数据库收缩后的数据库文件中所需的剩余可用空间百分比。如果使用 DBCC SHRINKDATABASE，可能需要重启 SQL Server。

使用 sp_spaceused 存储过程确定 tempdb 中当前正在使用的空间。然后，计算要用作 DBCC SHRINKDATABASE 参数的剩余可用空间百分比。此计算基于所需的数据库大小。
```
 备注在某些情况下，可能需要执行 sp_spaceused @updateusage = true 以重新计算所使用的空间并获取更新的报告。 有关详细信息，请参阅 sp_spaceused。
```
请考虑以下示例：

假设 tempdb 有两个文件：主数据文件 (tempdb.mdf) 为 1,024 MB，日志文件 (tempdb.ldf) 为 360 MB。假设 sp_spaceused 报告主数据文件包含 600 MB 的数据。此外，假设你想要将主数据文件收缩到 800 MB。计算收缩后剩余可用空间的所需百分比：800 MB - 600 MB = 200 MB。现在，将 200 MB 除以 800 MB = 25%，这就是你的 target_percent。事务日志文件相应地收缩，在数据库收缩后保留 25% 或 200 MB 的可用空间。
使用 SSMS、Azure Data Studio 或 sqlcmd 连接到 SQL Server，然后运行以下 Transact-SQL 命令。将 <target_percent> 替换为所需的百分比：
```
DBCC SHRINKDATABASE (tempdb, '<target_percent>');
```

tempdb 上的 DBCC SHRINKDATABASE 命令存在限制。数据和日志文件的目标大小不能小于创建数据库时指定的大小，也不能小于使用文件大小更改操作（例如使用 MODIFY FILE 选项的 ALTER DATABASE）显式设置的最后一个大小。 DBCC SHRINKDATABASE 的另一个限制是参数 target_percentage 的计算及其对所用当前空间的依赖。

5、使用 DBCC SHRINKFILE 命令

使用 DBCC SHRINKFILE 命令收缩各个 tempdb 文件。 DBCC SHRINKFILE 提供的灵活性比 DBCC SHRINKDATABASE 更高，因为你可以在单个数据库文件上使用，而不会影响属于同一数据库的其他文件。 DBCC SHRINKFILE 接收 target_size 参数。这是数据库文件所需的最终大小。

确定主数据文件 (tempdb.mdf)、日志文件 (templog.ldf) 和添加到 tempdb 的其他文件所需的大小。确保文件中使用的空间小于或等于所需的目标大小。

使用 SSMS、Azure Data Studio 或 sqlcmd 连接到 SQL Server，然后针对要收缩的特定数据库文件运行以下 Transact-SQL 命令。替换 <target_size_in_MB> 为所需的大小：

USE tempdb;
GO-- This command shrinks the primary data file
DBCC SHRINKFILE (tempdev, '<target_size_in_MB>');
GO-- This command shrinks the log file, examine the last paragraph.
DBCC SHRINKFILE (templog, '<target_size_in_MB>');
GO

DBCC SHRINKFILE 的优点是可以将文件大小缩减到小于其原始大小。可以在任何数据或日志文件发出 DBCC SHRINKFILE。不能使数据库小于 model 数据库的大小。

6、运行收缩操作时出现错误 8909

如果 tempdb 正在使用，并且你尝试使用 DBCC SHRINKDATABASE 或 DBCC SHRINKFILE 命令缩小它，则可能会收到类似于以下内容的消息，具体取决于所使用的 SQL Server 版本：

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (6:8040) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).