We have mount point, under the mount point, we have data1, data2, log1, log2, tempdbmdf1, tempdbmdf2, tempdblog1, tempdblog2,.....The tempdb's are pre-sized to say 20GB each, no auto growth set.
Log files are used sequentially, so there is no real advantage to having multiple log files
Getting ticket/alerts saying the tempdb is getting running out space. This has been going on for a wile now. When does the tempdb release space? I know rebooting is one option.(not an option for me)
Objects are destroyed/released from tempdb when the session/spid has completely finished and that spid is destroyed (or reused). In some cases you will notice a session is still active in tempdb and is taking up a lot of space because a developer has written a tempdb-intensive query, it completed, but his spid is still connected to the instance. While the query has completed, the spid is still active, therefore all of the objects/operations that were used in tempdb are still held in the database (they will not go away unless physically dropped (i.e. temp tables)) or the existing connection is closed.
What else can you explain about tempdb use?
Please refer to this tempdb whitepaper that offers some great things to bear in mind when dealing with tempdb!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience