How to shrink Tempdb

There may come a time when you might want to shrink tempdb because it has become too large.There are a few ways you can do this and I have listed them below but please read to the end of the post before making a decision on which way you want to approach this. There is an important note at the end of the post.

So first, we’ll look at the configuration on my server

SELECT name, size

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

name                 size

-------------------- -----------

tempdev              1280

templog               640

(2 row(s) affected)

Note that the size column is listing the size of the file in 8Kb pages. In this instance my “tempdev” file is 10Mb (( 1280 * 8 ) = 10240 kb)

How to shrink tempdb using DBCC SHRINKFILE

The syntax is as follows and the operation does not require a restart of the sql server service.

DBCC SHRINKFILE(logical_filename, size_in_MB)

So I will shrink the file to 5Mb

DBCC SHRINKFILE(tempdev, 5);

Which produces the following output and I can see that CurrentSize is now 50% smaller than previously

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages

------ ----------- ----------- ----------- ----------- --------------

2      1           640         288         176         176

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

name                 size

-------------------- -----------

tempdev              640

templog              640

(2 row(s) affected)

Don’t try and increase filesizes in tempdb using this command because you will see an error. In this example, the attempt was to increase to 50Mb.

Cannot shrink file ’1′ in database ‘tempdb’ to 6400 pages as it only contains 640 pages.

How to shrink tempdb using DBCC SHRINKDATABASE

The syntax is as follows and does not require a restart of the SQL Server service:

DBCC SHRINKDATABASE(tempdb, ‘target_percentage_of_free_space’);

So if the data files in tempdb had enough free space, you could shrink tempdb by running this command to leave 10% of free space at the end of the files:

DBCC SHRINKDATABASE(tempdb, 10);

How to shrink tempdb using ALTER DATABASE

You can use the ALTER DATABASE command to perform a tempdb resize. A restart of the SQL Server service will shrink tempdb to its original predefined size but you can also resize tempdb using ALTER DATABASE.

The following script will resize both the log and data file to be 100Mb.

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, SIZE=100Mb);

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, SIZE=100Mb);

GO

How to shrink tempdb using Management Studio

You can also use Management Studio to resize Tempdb and perform shrink operations by right mouse clicking the tempdb database and choosing Tasks->Shrink.

To resize tempdb, you can set the file sizes by right mouse clicking the tempdb and choosing Properties->Files and setting the sizes there.

Note that with both the ALTER DATABASE and management studio methods, you cannot resize a tempdb file to a particular size if the data contained in the file exceed the size that you are trying to size to.

Should you shrink TempDB?

It is documented in this Microsoft article that it can cause consistency errors in the TempDB database if you perform a shrink operation while the database is in use so please read this carefully and consider whether you can shrink the database by other means, i.e restarting the SQL Server instance which will create a brand new copy of TempDB releasing the disk space.

Troubleshooting Insufficient Disk Space in tempdb

Tempdb is a SQL Server system database that's a global resource available to all users. SQL Server uses tempdb to complete internal database operations. Tempdb is used for temporary user objects and internal database engine operations, including joins, aggregations, cursors, sorting, hashing, and row versioning. Unlike the data in a typical user database, the data in tempdb isn't persisted after the SQL Server instance is shut down.

Troubleshooting Insufficient Disk Space in tempdb :

Error messages that indicate insufficient disk space in the tempdb database. 

Error

1101 or 1105 Any session must allocate space in tempdb.

3959        The version store is full. This error usually appears after a 1105 or 1101 error in the log.

3967        The version store is forced to shrink because tempdb is full.

3958 or 3966 A transaction cannot find the required version record in tempdb.

The following examples show how to determine the amount of space available in tempdb, and the space used by the version store and internal and user objects.

Determining the Amount of Free Space in tempdb :

The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Amount Space Used by the Version Store :

The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Longest Running Transaction:

If the version store is using a lot of space in tempdb, you must determine what is the longest running transaction. Use this query to list the active transactions in order, by longest running transaction.

SELECT transaction_id

FROM sys.dm_tran_active_snapshot_database_transactions 

ORDER BY elapsed_time_seconds DESC;

A long running transaction that is not related to an online index operation requires a large version store. This version store keeps all the versions generated since the transaction started. Online index build transactions can take a long time to finish, but a separate version store dedicated to online index operations is used. Therefore, these operations do not prevent the versions from other transactions from being removed. For more information, see Row Versioning Resource Usage.

Determining the Amount of Space Used by Internal Objects :

The following query returns the total number of pages used by internal objects and the total space in MB used by internal objects in tempdb.

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],

(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Amount of Space Used by User Objects:

The following query returns the total number of pages used by user objects and the total space used by user objects in tempdb.

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Total Amount of Space (Free and Used):

The following query returns the total amount of disk space used by all files in tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB]

FROM tempdb.sys.database_files

Monitoring Space Used by Temp Tables and Table Variables :

You can use an approach similar to polling queries for monitoring the space used by temp tables and temp variables. Applications that acquire a large amount of user data inside temp tables or temp variables can cause space use problems in tempdb. These tables or variables belong to the user objects. You can use the user_objects_alloc_page_count and user_objects_dealloc_page_count columns in the sys.dm_db_session_space_usage dynamic management view and follow the methods described earlier.