How to Address TempDB Space Issue

You may have come across situations where the TempDB has grown very large, sometimes even completely running out of space.  When this happens, you need to shrink the TempDB.

DBCC SHRINKFILE ('tempdev', 1024)

A couple of problems sometimes comes up after doing this:
1. The TempDB shrinks successfully.  However, there is still a process running out there that will fill up TempDB again.
2. The TempDB does not shrink.

For the 1st issue, use the following query to check how much space is being used in TempDB.


SELECT
[TotalSizeOfTempDB (MB)] = 
SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count
+ unallocated_extent_page_count) * (8.0/1024),
[UsedSpace (MB)] = 
SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count) * (8.0/1024),
[FreeSpace (MB)] = 
SUM(unallocated_extent_page_count * (8.0/1024))
FROM sys.dm_db_file_space_usage
WHERE database_id = 2


Most likely, you will see the a large amount of used space, or the used space continue to grow.  To find the culpable transaction, run the following command, which will give you the SPID for the oldest open transaction.

DBCC OPENTRAN('tempdb')

With this SPID, you can now run the following to find the SQL command being executed as well as the user and machine executing it.

DBCC INPUTBUFFER (<SPID>)
SP_WHO2 <SPID>

From here, it should be easy to track down the person/process responsible and take the appropriate action to rectify.  It may be to stop a job on the front end application, re-write the SQL command to be more optimal, or even just kill the SPID.


For the 2nd issue, you may be unable to shrink TempDB.  The reason TempDB cannot be shrunk may be because there are uncommitted transactions or the proc/system cache needs to be cleared.  Re-starting the SQL Server instance will shrink the TempDB.  However, this is not always an option, especially in a production environment.  To get around having to restart the SQL instance, run the following to shrink TempDB.

USE tempdb
GO
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE ('tempdev', 1024)
GO