This article discusses different methods that you can use to shrink the tempdb database in Microsoft SQL Server. Before you shrink the tempdb database using the methods described in this article, note the following:

The tempdb size is reset to the final configured size (i.e. to the default size or the final size that was set using the alternate database) after each reboot. Therefore, unless you have to use different values ??or get the trì you do not have to use any of the procedures documented in this article. You can wait for the next SQL Server service restart for the size to decrease. A larger tempdb database size will not adversely affect the performance of SQL Server.

In SQL Server 2005 and later versions, shrinking the tempdb database is no different from shrinking the user database except for the fact that tempdb resets to its configured size after each startup. SQL Server instance again.

It is safe to run shrink in tempdb while tempdb operation is in progress. However, you may encounter other errors like blocking, delay, etc. may prevent shrinking from completing. Therefore, to ensure that tempdb shrinking is successful, we recommend doing this while the server is in single-user mode or when you have stopped all tempdb activity.

Tempdb information

The tempdb database is a temporary workspace. SQL Server uses tempdb to perform many tasks. Among those tasks are the following:

Store explicitly created temporary tables

Working tables hold intermediate results generated during query processing and sorting

Materialized static pointer

Store version records when the snapshot separation level or read-committed snapshot isolation level is used

SQL Server only records enough information in the tempdb transaction log to rollback a transaction, but does not redo the transaction during a database restore. This feature increases the performance of INSERT statement in tempdb. In addition, you do not have to record information to re-execute any transactions vì tempdb is regenerated each time you restart SQL Server. Therefore, there are no transactions to upgrade or revert. When SQL Server starts, tempdb is recreated using a copy of the sample database and reset to the last configured size. The configured size is the final explicit size set by a file resize operation such as ALTER DATABASE using the MODIFY FILE option or the DBCC SHRINKFILE or DBCC SHRINKDATABASE statement.

By default, the tempdb database is configured to grow automatically as needed. As a result, this database may unexpectedly grow in time to a size larger than the desired size. A simple restart of SQL Server will reset tempdb's size to the last configured size.

In SQL Server 2005 and later, you can use any of the following methods to change the size of tempdb.

Note SQL Server Management Studio in SQL Server 2005 does not indicate the exact size of the tempdb files after the shrink operation. The value "Currently allocated space" is always taken from the sys.master_files DMV and it is not updated after the shrink operation has taken place for the tempdb database. To find the exact size of the tempdb files after the shrink operation, execute the following command in SQL Server Management Studio:

use tempdb

go

select (size*8) as FileSizeKB from sys.database_files