Welsh Corgi (8/31/2015)
Eric M Russell (8/31/2015)
TEMPDB will automatically shrink whenever SQL Server is restarted, which might be most reliable way to do this, if needed. However, unless your TEMPDB is competing for storage with other databases, then there is no practical reason to shrink it ever. Ideally, TEMPDB should be allocated on a dedicated disk of it's own and just allowed to grow as needed.Thank you.
Well I had no disk space available. Before I made my post I was able to free up 13.6 of Disk Space.
I had to restart SQL Server.
There was not a lot of activity on the Server.
tempdb is on a Disk of it's own.
Unless it's necessary to free up space on that disk for something other than TEMPDB, then there is no reason to shrink the file. The allocation just gets reused, similar to other .mdf database files, transactin log files, or the Windows swap file.
On one database, I've got a 1 TB TEMPDB file sitting on a 1 TB dedicated disk. A buggy ETL process filled up the disk months ago leaving that tempdb file bloated to the max. I've since refactored the ETL process so it doesn't utilize but a small fraction of tempdb, but since the server hasn't been rebooted, the file is still maxed out. But it doesn't matter; it's actually beneficial not to have the file shrinking and auto growing. Having the tempdb file consuming the entire disk just ensures that no one else has an opportunity to place database files or whatever on a drive that's intended only for tempdb in the first place.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho