Sql-server – tempdb not releasing space

shrinksql servertempdb

I have 1 tempdb mdf file, and 7 ndf files. They all grew to 63 GB, and filled up the 500 GBdrive (the .mdf and .ndf files say 99% free space.).

I ran sp_who2 'Active' and there is no active transaction using tempdb. Why can't I release space?

I cannot restart this server till Friday, which is our designated maintenance window. What should I do until then?

Update
So I got permission to reboot the server. However it did not free up the space…tempdb files are still big. What do I do?

Best Answer

They all grew to 63 gb, and filled up the 500 gb drive
Why can't I release space?

To be clear, the files won't "shrink" back to their previous size on their own while the system is running. They should go back to their configured size after a reboot (since tempdb is recreated when the database engine starts up).

You can check the configured size vs. actual size using these queries from Andy Mallon's blog:

--Check sys.master_files in master
USE master
GO
SELECT DataFileName = mf.name, 
       FileSizeMB  = mf.size*8/1024
FROM sys.master_files AS mf
WHERE mf.database_id = db_id('tempdb')
ORDER BY mf.type, file_id;
GO
--Check sys.database_files in tempdb
USE tempdb
GO
SELECT DataFileName = dbf.name, 
       FileSizeMB  = dbf.size*8/1024
FROM sys.database_files AS dbf
ORDER BY dbf.type, dbf.file_id;

The values from sys.master_files are the ones that tempdb should return to after a reboot. If those values have increased to 63 GB, then someone has configured them that way.

If you choose the shrink the files, be sure to heed Andy's suggestion:

If you do shrink a tempdb file, check the sys.master_files metadata before & after to ensure you leave it in the ideal state. Use ALTER DATABASE...MODIFY FILE to repair the metadata for the next restart

Once you have the immediate size problem addressed, you really need to:

  • identify the root cause (what queries are causing tempdb) to grow, see the Q&A here: How to find the SQL statements that caused tempdb growth?
  • set the tempdb data files to a fixed size, disabling autogrowth
  • preferably move them to their own drive (if they are not already on a dedicated drive), to prevent this from causing user databases or the server from being as impacted
Related Question