October 19, 2010 at 1:12 pm
My tempdb raised to 25 GB since i was executing a query but even after execution of the query the size of tempdb remains same can anyone help me to bring it back to normal
October 19, 2010 at 1:22 pm
TemDB will not automatically shrink back, but you need to take something bigger in consideration: how often will this happen? If it will be frequent, it would be best to leave as is, if possible, because the constant growing and shrinking will cause other issues (disk fragmentation, performance, etc).
October 19, 2010 at 1:29 pm
Richard M. (10/19/2010)
TemDB will not automatically shrink back, but you need to take something bigger in consideration: how often will this happen? If it will be frequent, it would be best to leave as is, if possible, because the constant growing and shrinking will cause other issues (disk fragmentation, performance, etc).
I'd go 1 step further and set the base file size to 25 GB that way you always have that size on each server restart.
October 19, 2010 at 1:31 pm
premkuttan.lakshmanan (10/19/2010)
My tempdb raised to 25 GB since i was executing a query but even after execution of the query the size of tempdb remains same can anyone help me to bring it back to normal
TempDB can be shrunk like other databases, but depending on it's use it doesn't always shrink. If you do a service restart it will go back to it's predefined size. BUT before you do the restart it's a good idea to work out what you want TempDB to actually look like and modify it appropriately.
Typical good design includes having one data file per logical CPU and making the total minimum size about 20% of your largest database.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
October 19, 2010 at 2:10 pm
Check the query you are running may be they are using a temp table or temp variable
to load data temporarily.
If is that the case then you should change the query or increase the size of temp db or set it to auto growth.
Small tip make sure you close the session after running queries.
that release the memory allocated for that session and query.
I hope its help full.
If you have any contention issues on temp db add and extra file to it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply