I added several indexes to a SQL Server 2012 database to help with performance a couple of days ago. Today our partition with TempDB on it ran out of space. TempDB went from less than 10 GB to 38 GB. after running :

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

it looks like I have about 22GB of free space in tempDB. I understand that TempDB was used to do all of the sorting that was done by SQL Server but now it is causing space issues. If TempDB has that much free space why is it complaining about running out of space on the server? What can I do to prevent this problem from happening again?

  • I remember in SQL 2008 there was a bug where if all of your tempdb data files weren't the same size, only 1 would grow. I would be shocked if it wasn't taken care of in 2012, but have you checked all the file sizes? Maybe tempdb did fill up during a very busy period, also disk space was out, but when you checked it tempdb was purged and small again. Nov 1 '12 at 17:03

Even though you have 22 GB of free space in tempdb now, that doesn't mean this space wasn't used before. Something caused autogrowth to kick in, and once that process ends, there isn't anything to shrink tempdb back to its current size (and I'm not even going to talk about autoshrink...).

It sounds like the thing complaining about running out of space was the drive (or maybe it hit a hard limit that was set in SQL Server--I can't quite tell from your wording, but I'm guessing the drive). It recognizes that you have a 38 GB tempdb file, but the Windows API has no clue about whether disk space allocated to an MDF or LDF file is "free space" or not; it's all considered in use by Windows, so it all counts.

The best way to prevent this problem happening is to figure out what caused your tempdb to grow and size appropriately. If it's a one-time event (e.g., creating indexes with sort_in_tempdb on), tempdb will go back to its default size the next time you have to restart SQL Server. But if it's as a result of a regular job or a change in queries that have caused significantly more tempdb usage (e.g., a marked increase in hash join spillovers), you would need to take this as your new high water mark and try to get some more disk space.

To help figure out which one it is, you can start with Mark Storey-Smith's answer to a previous question of similar nature.

  • Voting up this answer, same thing I mentioned in my reply but with more info. Thanks! Nov 1 '12 at 17:03
  • 1
    +1 SSMS actually has a built in report which will show when the data file(s) grew and by how much. In SSMS, Right click tempdb->Reports->Disk Usage . Any autogrowth since the last service start should show up in the report.
    – brian
    Nov 1 '12 at 17:54

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.