December 25, 2002 at 1:47 pm
Hello to all and happy holidays.
I got the following problem with a tempdb. It went from 3 GB to 16 GB in one day. I did try to shrink it. It did not (the log is OK, the MDF is the one with the problem.)
Then, I stopped and resarted SQL Server (SQL Server 2K SP2) which usually shrink the tempdb. It didn't work and now is 36 GB and still growing.
I am going to reboot the server to see if that helps. In the mean time any suggestions? Thank you
December 25, 2002 at 3:14 pm
Thats a lot of growth! Might try stopping service and deleting tempdb (rename the mdf is safer), let SQL create a new one from model. If it grows that much again, you'll have to profile to see what is causing the growth. Might want to put a size limit on both mdf/ldf just in case, better to stop it before it fills the drive.
Andy
December 25, 2002 at 4:20 pm
Might want to also run Profiler and see what's hitting tempdb so hard.
Steve Jones
December 26, 2002 at 3:33 am
Thank you. Rebooting the server did the trick. Still, I will profile to figure out what is going on there and will keep renaming tempdb and let the system create a new one as alternative.
Thank you again. Have a great 2003!!! Keep up the good work guys!! You have been my right hand all this 2002!!!!
December 30, 2002 at 12:31 pm
I ran into a problem with the TEMPDB trransaction log growing so what I did was 1)set a limit on it's growth and 2) have a job run every 10 minutes which performs a CHECKPOINT in the TEMPDB database. This flushes the TEMPDB's transaction log.
December 30, 2002 at 1:53 pm
Should checkpoint automatically unless you have the recovery interval set pretty high, default is about a min.
Andy
December 31, 2002 at 5:29 am
quote:
Thats a lot of growth! Might try stopping service and deleting tempdb (rename the mdf is safer), let SQL create a new one from model. If it grows that much again, you'll have to profile to see what is causing the growth. Might want to put a size limit on both mdf/ldf just in case, better to stop it before it fills the drive.Andy
http://www.sqlservercentral.com/columnists/awarren/
One thing to note here. TempDB is deleted and recreated every time SQL Server is stopped. So don't worry with renaming. Occasionally the file can be locked at the OS level which may require a full reboot. If it is growing often to the same size you may want to leave that way as you are hurting performance if it has to grow constantly. Usually it sizes itsefl nicely. But keep in mind when you do and order by or create a temp table and several other things, this information is stored in the tempDB. This is why it is suggested to be on it's own drive away from other DBs. The big question here is how big is your biggest DB and in it how big is your biggest table. This should be the smallest your tempDB is for safety of order by (sorry I forget where I found this but it was within the MS KB site I know for sure). You need to dteremine first why the high growth before you limit it. ANd make sure you have the recover set to Simple and the only option on be default are Auto update stats and Auto create stats, these should be fine.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply