Unanswered: Tempdb size shrinking when it happend ?
On what scinarios the size of the Tempdb will come to the normal stage.
The situation is I have run a sqlscript in QA after running the same I have observed that the size have increased to 500 MB .
Even though the squery have completed successfully since a long time the size of the Tempdb is not comming to the normal size.
I would like to know Generally on what condition the size of the Tempdb will come to normal state.Like after a duration of how long this comes to the normal state.
Since restarting the server is not possible i am seeking for some alternative solution.
Is sql server automatically takes care of this .. ie to shrink the database ?
I don't know the exact parameters for when SQL will shrink TempDB (how often, to what size, etc). I DO know that you MUST set a max size limit or you will get into big trouble when it grows out of control and soaks up all your HD space.
It's size will vary depending on loads and other things. 500MB doesn't seem too bad to me. 'Course I don't know anything about the rest of your dbs.
The only way to shrink tempdb size, the safest way I mean, is to stop and restart SQL. If that's not a option, then just leave it alone. Your process won't have to increase the size of tempdb when they run, so it will save you some overhead. The rule that I follow in the last few years and it has worked quite well is to set the tempdb as big as the RAM. Of course, everyone has different situations and different setups they need to take care of.
As a general rule of thumb, database products won't "shrink" their temporary files. Changing the size of a file is an expensive operation. Programs will usually increase the size of the file when they must (often growing the file by a large chunk, more than they immediately need), and then recycle the space using their own internal logic. The file never actually shrinks.
Also as a general rule of thumb, don't worry about it. If available disk-clusters exist for the storage of data, it really doesn't matter if they're inside an existing file or not. If you find yourself running out of storage capacity, buy more drives. Seriously.
Autogrowth allows the server to acquire additional space for the specified file based on increment unit of measurement (percent or MB.) If the file grew, then stopping and starting the service is not going to release unused allocated space. If you look at the file size after bouncing the service it'll be of the same size as before. For user databases you can set Autoshrink setting on and it'll take care of releasing the allocated space back to OS if it's not used. In the case of tempdb this option is not available, so you can use Shrink database option or as mealejr suggested, - dbcc shrinkfile/shrinkdatabase/alter database modify file command line utilities.