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
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;
--Check sys.database_files in tempdb
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