tempdb数据库清理_如何检测和防止TempDB数据库意外增长

tempdb数据库清理

In this article, we will discuss the best practices that should be followed in order to keep the TempDB database in a healthy state and prevent any unexpected growth of the database, in addition to the procedure that can be followed to detect this unexpected growth once occurred.

在本文中，我们将讨论为了使TempDB数据库保持健康状态并防止数据库出现意外增长而应遵循的最佳实践，以及可以检测到意外增长发生的过程。

TempDB概述 (TempDB overview)

The TempDB database is one of the most important SQL Server system databases, that is used to store temporary user objects, such as the temporary tables that are defined by the user or returned from table-valued function execution, temporary stored procedures, table variables or indexes.

TempDB数据库是最重要SQL Server系统数据库之一，用于存储临时用户对象，例如由用户定义或从表值函数执行返回的临时表，临时存储过程，表变量或索引。

In addition to the user objects, TempDB will be used to store internal objects that are created by the SQL Server Database Engine during the different internal operations, such as intermediate sorting, spooling, aggregate or cursor operations.

除用户对象外，TempDB将用于存储由SQL Server数据库引擎在不同的内部操作（例如中间排序，假脱机，聚合或游标操作）期间创建的内部对象。

The TempDB system database is used also to store the rows versions in order to support the features that require tracking the changes that are performed on the table rows, such as the snapshot isolation level of the transactions, Online Index rebuilds or the Multiple Active Result Sets feature.

TempDB系统数据库还用于存储行版本，以支持需要跟踪对表行执行的更改的功能，例如事务的快照隔离级别，联机索引重建或多个活动结果集特征。

TempDB database will be dropped and recreated again each time the SQL Server service is restarted, starting with a new clean copy of the database. Based on that fact, all the user and internal database objected that are stored on this database will be dropped automatically when the SQL Server service is restarted or when the session where these objects created is disconnected. Therefore the backup and restore operations are not available for the TempDB.

每次重新启动SQL Server服务时，将从数据库的新干净副本开始，删除并重新创建TempDB数据库。 基于这一事实，当重新启动SQL Server服务或断开创建这些对象的会话时，将自动删除存储在该数据库上的所有用户和内部数据库对象。 因此，备份和还原操作不适用于TempDB。

为什么选择TempDB (Why TempDB)

The TempDB system database plays an important role in SQL Server performance tuning process. Because it is used as caching storage to store different types of user database objects and to store the system internal objects in order to speed up the SQL Server Database Engine related processes.

TempDB系统数据库在SQL Server性能调整过程中起着重要作用。 因为它被用作缓存存储来存储不同类型的用户数据库对象并存储系统内部对象，以便加快与SQL Server数据库引擎相关的过程。

Due to this vital role that the TempDB system database plays in enhancing SQL Server instance overall performance, it is very important to apply the initial size, auto-growth and location best practices on the TempDB database data and log files. Applying these best practices in a way that fits your SQL Server instance workload will prevent expanding the TempDB database data and log files very frequently, taking into consideration that the file expands process is an expensive process in which SQL Server Engine will request extra space from the operating system, that will perform zeroing on that space before allocating it to SQL Server Engine. You can imagine the time and resources required for this allocation process.

由于TempDB系统数据库在增强SQL Server实例整体性能中起着至关重要的作用，因此在TempDB数据库数据和日志文件上应用初始大小，自动增长和位置最佳实践非常重要。 以适合您SQL Server实例工作量的方式应用这些最佳实践将防止非常频繁地扩展TempDB数据库数据和日志文件，同时考虑到文件扩展过程是一个昂贵的过程，在此过程中，SQL Server Engine会从中请求额外的空间。操作系统，它将在将该空间分配给SQL Server Engine之前对该空间执行清零。 您可以想象此分配过程所需的时间和资源。

If you already apply all best practices and an unexpected growth operation occurred, you can speed up the allocation process by using the database instant file initialization option, in which the operating system will trust the SQL Server service and allocate the required space without performing the time and resources wasting operation on the allocated space.

如果您已经应用所有最佳实践并且发生了意外的增长操作，则可以使用数据库即时文件初始化选项来加快分配过程，在该选项中，操作系统将信任SQL Server服务并分配所需的空间而无需执行时间以及资源浪费在分配的空间上。

最佳实践 (Best practices)

There are a number of TempDB system database configuration best practices that are recommended in order to get the best performance from that shared resource and protect your SQL Server instance from any unexpected growth in the TempDB database data or log files that may fill the underlying disk space.

建议使用许多TempDB系统数据库配置最佳实践，以从该共享资源中获得最佳性能，并保护您SQL Server实例免受可能填充基础磁盘空间的TempDB数据库数据或日志文件中任何意外增长的影响。

These configurations inclu