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.
DBCC FREESYSTEMCACHE ('ALL')
DBCC SHRINKDATABASE(tempdb, 10)
DBCC SHRINKFILE ('tempdev')
DBCC SHRINKFILE ('templog')
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.
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.
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.
Flushes the distributed query connection cache used by distributed queries (queries between servers).
DBCC SHRINKDATABASE(tempdb, 10)
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).
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
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
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.