1

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.

MetaGuru
  • 215
  • 1
  • 3
  • 8

2 Answers2

2

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_comment_is_not_funny
  • 523
  • 3
  • 8
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.

Neghtasro
  • 482
  • 3
  • 11