SQL Server Shrinking Tempdb- Script and Troubleshooting
Occasionally, you may need extra space in the tempdb database to accommodate operations such as performing a bulk insert or index maintenance. Another common reason for needing to shrink tempdb is temporarily using the extra disk space for another task (moving or writing a backup file for example.) Since this isn’t regular activity, you can shrink the tempdb files back down to an appropriate size after the work is finished. Unlike User Database Datafiles, shrinking the tempdb datafiles will not cause any fragmentation or performance issues since it only holds temporary objects and not actual data.
The code below is an example of shrinking a tempdb datafile. You will need to change the logical name, which you can find in the database properties, and the size you want it to shrink to in megabytes. Be careful and note that you should shrink every tempdb datafile down to the same size for best performance. By default, there is 1 datafile, but best practice suggests there should be 1 datafile per CPU core up to 8. (YMMV)
Example Shrink Script For All 4 Of My Tempdb Datafiles:
Detect Tempdb Files:
SELECT name, physical_name, ((size*8)/1024) AS [Size in MB] FROM sys.master_files WHERE database_id = 2 ORDER BY 'name';
Tempdb Properties:
For a quicker solution, below is a custom script that detects all tempdb datafiles and creates the shrink statements for you to execute in the ‘messages’ column. You will need to change the size value to what you want the datafiles shrunk to.
Shrink All Tempdb Datafiles Script:
---Change the size in MB to shrink to---
DECLARE @size NVARCHAR(10) = 1024
----------------------------------------
DECLARE @info nvarchar(max)
DECLARE @file nvarchar(max)
DECLARE @q1 nvarchar(max)
DECLARE tempdb_cursor cursor for
SELECT NAME FROM sys.master_files WHERE database_id = 2 AND NAME !='templog';
OPEN tempdb_cursor
FETCH NEXT FROM tempdb_cursor into @info
while @@fetch_status = 0
BEGIN
SET @info = @info
SET @q1 = 'USE [tempdb] DBCC SHRINKFILE (''' + @info + ''' , ' + @size + ')'
--EXEC @Q1
PRINT @q1
FETCH NEXT FROM tempdb_cursor
INTO @info
END
CLOSE tempdb_cursor;
DEALLOCATE tempdb_cursor;
Results:
If you encounter an error similar to the one below, then there is a lock on a tempdb object that won’t release and will prevent you from shrinking the files any further.
There are 3 possible solutions to this, but I recommend you proceed with caution before attempting them, since they can all have consequences.
- Find the process using tempdb and kill it. Make sure to verify what the process is and its level of important before stopping it.
- Run DBCC FREEPROCCACHE. This is generally not recommended since it empties out the cache of plans SQL has built up and will cause all queries to run more slowly than usual until the cache is built back up. However, it can also free the tempdb database to allow your shrink.
- If the last two options did not work, restarting the SQL instance should do the trick. This will of course kill all connections and interrupt any running jobs, but it guarantees that tempdb will be accessible since this database is recreated with each instance startup.
Thanks for reading! I hope this post helps you get the extra space you need in your tempdb database.