4

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?

JustinDoesWork
  • 478
  • 1
  • 5
  • 14

1 Answers1

6

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.

Community
  • 1
Kevin Feasel
  • 2,950
  • 16
  • 14