Shrinking The tempdb System Database Without Restarting SQL Server

Monitoring the tempdb system database is an important task in administering any SQL Server environment. From time to time this system database may grow unexpectedly.  Though numerous factors can lead to excessive growth of the tempdb database I have found the most common factor tends to be related to sorting that requires more memory than has been allocated to the SQL Server, which forces the work to be pushed to tempdb.  This can actually be caused by running open queries or even poorly written SSRS reports that allow the user to run the report without limits.  Additionally, any large result sets involving UNION, ORDER BY, GROUP BY, CARTESIAN JOIN, OUTER JOIN, CURSOR, temp tables, table variables or hashing can push work to the tempdb and result in its growth.

The simplest, though not always the most applicable method for getting the tempdb database to shrink is to restart the instance of SQL Server.  However, this may not be an option for many production environments.

Fortunately there is a way to shrink tempdb without taking the server offline.  However, there are some factors that should be considered when applying this method which I have outlined below.  The following script can be used to manage this process.

The above script includes multiple options that can contribute to reducing the size of the tempdb system database. However, you may not need to execute all available commands. Below I have included a brief overview of each command.

DBCC FREEPROCCACHE

Clears out the procedure cache.  This can free up some space in the tempdb but will clear out all cached execution plans, which will need to be rebuild the next time the procedure is called and will require all ad hoc queries and stored procedures to recompile the next time they are executed.  This can result in a temporary performance hit each time a procedure is being called the first couple of times.

DBCC DROPCLEANBUFFERS

Flush cached indexes and data pages.

DBCC FREESYSTEMCACHE (‘ALL’)

Clears the plan cache for the instance of SQL Server.  Clearing the plan cache will cause a recompile of all subsequent execution plans and can result in a temporary decrease of query performance.

DBCC FREESESSIONCACHE

Flushes the distributed query connection cache used by distributed queries (queries between servers).

DBCC SHRINKDATABASE(tempdb, 10)

Shrinks the tempdb database by a given percentage.  In this case 10%.  The database can not be made smaller than the minimum size of the database specified when the database was originally created.  The SHRINKDATABASE command can be stopped at any point in the process with all completed work being retained.

To view the current amount of free space in the database run sp_spaceused while connected to tempdb. The first set of output will display the size (in MB) of the current database (both data and log files) as well as space in the database that has not been reserved for database objects (unallocated space).

tempdb_sp_spaceused_output

The second set of output will display the total amount of space allocated by objects in the database (reserved), total amount of space used by data (data), total space used by indexes (index_size) and the total amount of space reserved for objects in the database that has not been used (unused).

DBCC SHRINKFILE (‘tempdev’)
DBCC SHRINKFILE (‘templog’)

Shrinks the size of the specified data or log file for the current database.  Make sure to include USE [tempdb] or manually specify the database in Management Studio prior to execution.  The SHRINKFILE operation can be stopped at any point in the process with all completed work being retained.

In addition to tempdev and templog, depending on your instance of SQL Server, you may have additional tempdb files that need to be included.  If so, simply replicate the above SHRINKFILE command and include each additional tempdb file.

Make sure you do not have any open transactions when running a SHRINKFILE command. Open transactions may cause the operation to fail and could potentially corrupt the tempdb system database.  Though this is certainly a worst case scenario, its a pretty bad one so just use some caution.

It is best to fully understand these options before taking any action to shrink your tempdb system database. However, if tempdb is growing and nearing the point where there is no available space and restarting the instance of SQL Server is not an option, the steps outlined above are likely your best bet for getting the size down to a more manageable number.