数据文件shrink_SQL Server中的Shrink TempDB数据库概述

数据文件shrink

This article explores the usage of TempDB and different ways to shrink the TempDB database in SQL Server

本文探讨了TempDB的用法以及在SQL Server中缩小TempDB数据库的不同方法

Each instance of Microsoft SQL Server has a system database TempDB. It is a backbone for the SQL Server instance. Let’s review the TempDB database and its usage with ways to shrink the TempDB database in the further sections of this article.

Microsoft SQL Server的每个实例都有一个系统数据库TempDB 。 它是SQL Server实例的骨干。 让我们在本文的其他部分中回顾一下TempDB数据库及其用法，以及如何缩小TempDB数据库的方法。

关于TempDB的一些要点 (Few important points about TempDB)

• TempDB is a global resource (available for all connected user) system database
  TempDB是全局资源（可供所有连接的用户使用）系统数据库
• SQL Server recreates the TempDB database each time SQL Service restarts. During restart, it takes a copy of MDF and LDF from the model database. The size and number of MDF and LDF files reset to preconfigured size
  每次重新启动SQL Service时，SQL Server都会重新创建TempDB数据库。 重新启动期间，它将从模型数据库中获取MDF和LDF的副本。 MDF和LDF文件的大小和数量重置为预配置的大小
• SQL Server does not perform recovery on the TempDB, and previous data is lost
  SQL Server不会在TempDB上执行恢复，并且以前的数据会丢失
• TempDB database is always in the Simple recovery model, and we cannot take database backup for it
  TempDB数据库始终处于简单恢复模型中，因此我们无法为其进行数据库备份
• We cannot roll back transactions in the TempDB because it minimally logs the transactions
  我们无法在TempDB中回滚事务，因为它最少地记录了事务

TempDB使用情况摘要 (TempDB Usage Summary)

• Usually, we create local temporary tables (# naming conventions) and global temporary tables (## naming conventions) to prepare intermediate tables. SQL Server creates those temporary tables in the TempDB database
  通常，我们创建本地临时表（＃命名约定）和全局临时表（##命名约定）以准备中间表。 SQL Server在TempDB数据库中创建这些临时表
• We can create or rebuild an index in TempDB using the SORT_IN_TEMPDB= ON clause. SQL Server performs all sorting calculations in the TempDB instead of the database on which the object belongs
  我们可以使用SORT_IN_TEMPDB = ON子句在TempDB中创建或重建索引。 SQL Server在TempDB中而不是对象所属的数据库中执行所有排序计算
• SQL Server uses the TempDB for the Read COMMITTED SNAPSHOT isolation level. SQL Server uses the row versioning for each record. The old version also gets an additional 14 bytes in the TempDB to track the row versioning
  SQL Server将TempDB用于读取已提交快照隔离级别。 SQL Server对每条记录使用行版本控制。 旧版本还在TempDB中获得额外的14个字节来跟踪行版本控制
• Internal objects such as Cursor work tables, Spool operations, Intermediate sorts operations such as GROUP BY, ORDER BY, UNION, DBCC CHECKDB, Temporary large object storage, Service broker event notification
  内部对象，例如游标工作表，假脱机操作，中间排序操作，例如GROUP BY，ORDER BY，UNION，DBCC CHECKDB，临时大对象存储，Service Broker事件通知
• In the Multiple Active Result Sets (using MultipleActiveResultSets=True), SQL Server uses the versioning and stores that in the TempDB
  在多个活动结果集中（使用MultipleActiveResultSets = True），SQL Server使用版本控制并将其存储在TempDB中

You can go through these articles, Configuration, operations and restrictions of the TempDB SQL Server system database and How to monitor the SQL Server TempDB database to get more details on the TempDB database.

您可以阅读以下文章， TempDB SQL Server系统数据库的配置，操作和限制以及如何监视SQL Server TempDB数据库以获取有关TempDB数据库的更多详细信息。

收缩TempDB活动概述 (Overview of Shrink TempDB activity)

Starting from SQL Server 2016, the installation wizard automatically detects the CPU cores and creates the necessary number of database files for the TempDB. It also sets the maximum growth of individual files to 64 MB.

从SQL Server 2016开始，安装向导将自动检测CPU内核并为TempDB创建所需数量的数据库文件。 它还将单个文件的最大增长设置为64 MB。

In the following screenshot, you can see the TempDB configuration for my demo SQL instance.

在以下屏幕截图中，您可以看到我的演示SQL实例的TempDB配置。