0

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?

  • Databases don't just "release" the space they have used after they don't need it. For a user database, that would be a huge concern, as fragmentation could easily occur. For tempdb when you restart the instance, and the database is rebuilt, it'll be released then. – Larnu Jul 15 at 14:39
  • 1
    If you can't restart the intance, you could shrink it while it's online. There is documentation on this, such as How to shrink the tempdb database in SQL Server. I would, however, also suggest you put size limits on the files, so that this doesn't happen again, or migrate your tempdb to it's own storage area. – Larnu Jul 15 at 14:48
  • 1
    You should check if there is space free in tempdb or if it is full. A full tempdb is a problem, a tempdb with space available can wait until the maintenance window (or after - it will probably just grow back anyway). – Mo64 Jul 15 at 14:57
  • 3
    am2.co/2020/04/stop-trying-to-shrink-tempdb - do you know why they all grew to 63GB? If you shrink the files to get the space back (temporarily), what are you going to do with that space you got back, since the same thing could just happen again tomorrow? – Aaron Bertrand Jul 15 at 15:15
  • 1
    Then either tempdb auto-grew immediately at startup (not likely) or somebody configured tempdb to be created with that size at startup. SSMS unfortunately sucks for managing tempdb, so use my blog post post instead: sqlblog.karaszi.com/managing-tempdb – Tibor Karaszi Jul 15 at 20:40
1

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.