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?
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.