December 3, 2008 at 5:28 am
Hi, We are running SQL2000 SP4 (2050) on windows 2003 server 32bit. I have noticed that the tempdb (NOT THE LOG) space used is getting larger over time. We have set aside 3Gb for the tempdb. On Monday we had 300mb used in the tempdb today we have 1.3GB used. The log is empty!
Is there a way to see why this is happening and to fix it?
thanks,
December 3, 2008 at 5:32 am
check what transcations are going on, Log size grows when there is a huge transcation going on, you can use profiler to check what is happening on background
December 3, 2008 at 8:18 am
It is the database file NOT the log file that has the space growing.
The log file is empty, but I see over 1GB of space used in the tempdb.mdf file.
Is there a way to free the space in temp DB without restarting SQL?
December 3, 2008 at 3:04 pm
Check for any open transactions in it. If none then it might allow you to shrink the database.
MJ
December 3, 2008 at 3:42 pm
Tempdb will grow as large as is needed to support the queries being run on your system. Once the database has grown - it will not shrink until you restart SQL Server.
With that said - how large are your databases on this instance? As a general rule - you should size tempdb at 20% of the size of your user databases, and adjust that size as needed for your environment.
If your application does not utilize tempdb a lot - setting tempdb to 2000MB could be more than enough. However, if your applications utilize tempdb a lot - you might need to size it much larger.
FWIW - I have one system where I have 8 files for tempdb at 10GB for each file. This system supports multiple user databases with the largest currently at 350,000MB and growing at ~6GB per month.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply