0

So my tempdb is getting huge. Like 200+ GB.

USE tempdb  GO  EXEC sp_spaceused

database_name   database_size   unallocated space 
tempdb          211668.88 MB    206803.45 MB

It's using a SIMPLE recovery model, which according to Microsoft:

Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small.

But does 'reclaim log space' mean that it still maintains the disk allocation?

Kind of like if I had an influx of patrons at my business so I borrowed a lot across the street to put the extra cars, but then once I no longer need all that space I still reserved that extra parking lot for later (didn't allow anyone else to use it) despite it remaining empty?

If this is normal behavior then I might expect that it's hording space that it thinks it might use again one day, and thus if it had 'infinite' space to expand it would eventually stop growing based on the determined usage patterns?

Or, is it suppose to be giving back disk space to the OS when it releases?

UPDATE

Also found this: http://technet.microsoft.com/en-us/library/ms176037(v=sql.105).aspx

By default, the tempdb database automatically grows as space is required, because the MAXSIZE of the files is set to UNLIMITED. Therefore, tempdb can continue growing until space on the disk that contains tempdb is exhausted. You can prevent tempdb from growing without limits by setting a MAXSIZE for tempdb, however this is not recommended. Limiting the size of tempdb may cause the database to run out of disk space. This can cause significant disruptions in your production environment and can prohibit applications that are running from completing operations.

So maybe I've answered my own question here but hopefully someone can clarify: Does it then follow that I should have it on a drive/partition by itself? Then it can grow 'as big as it can' while at the same time not taking up disk space that I want to use for other things? But in that case, I wonder what the difference is between MAXSIZE and actually not having any more space to physically grow.

  • 1
    Simple recovery means that the log space within the log file is more readily reused. It does not mean the file size itself will magically shrink (and you don't want that). Please read this in its entirety. – Aaron Bertrand Nov 19 '14 at 19:46
  • Tempdb recovery model is always simple but logging is minimal. One can say that even though recovery model is simple transactions are logged minimally – Shanky Nov 19 '14 at 23:32
0

Yes, SQL Server files do not shrink automatically. They remain the same size unless you explicitly shrink them, either through the SQL Server Management Studio or by using the DBCC SHRINKFILE command. While you can set files to autoshrink, this is widely advised against because shrinking/growing files is very resource intensive, especially when you're dealing with log files which don't have instant file initialization.

EDIT: In response to your edit: there is a difference between MAXSIZE and the size a file can get before running out of disk. You can set that in the Files section of the database properties, or with an ALTER DATABASE command.

  • Also, do not confuse the log size with the database size itself - if you performed a large transaction then it will increase as needed to handle tempdb objects created. Neither will be shrunk automatically, but the log of the tempdb is probably of less concern than the data file size. – LowlyDBA Nov 19 '14 at 19:40
  • Hmm OK. So it seems like the fix is as simple as just giving it more disk space (besides obviously fixing the usage to not require so much tempdb). I wasn't sure if it would always just take up all space that I gave it, but it seems like once I really give it 'enough' space, it should stop growing. – MetaGuru Nov 19 '14 at 19:41
  • Yeah, giving it disk is your best bet. You can limit the size, and I edited in information about that, but it's best to make sure tempdb is able to grow and has the space to do so- otherwise, things break. – Neghtasro Nov 19 '14 at 19:42
  • 1
    @ioSamurai in regards to your update, it is best practice to have it on a dedicated partition. – LowlyDBA Nov 19 '14 at 19:42
  • @Neghtasro just to make more clear Instant file initialization works differently for tempdb log file. Please edit your answer. You can say its there for tempdb log files in bit different way read:sqlskills.com/blogs/jonathan/… – Shanky Nov 19 '14 at 23:31
0

Tempdb will release space when you restart SQL Server or when you shrink the files, refer to kb307487. As was mentioned, it is usually a bad practice to use autoshrink. If you are under space constraints because tempdb wasn't provisioned separate storage, you can shrink the data files using dbcc shrinkfile. If you don't have to do that, I wouldn't. It seems you have a workload that needs the space and it having the space already allocated prevents it from having to grow again. Microsoft has some guidance on how to improve performance of tempdb.

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.