February 1, 2006 at 5:57 pm
We have a sql 2000 server backend (through a firewall) to our public webserver. Normally we have around 40GB of free disk space. This morning I found 0 bytes free! I deleted our backups from disk (on tape by now) and the free space went up to 40GB again. The size of the files deleted didn't add up to 40GB but at least the server was back to normal. I checked this afternoon to find we had only 8MB free. I traced it further and found that our tempdb database (on autogrow) had grown (data and log) to about 40GB! I couldn't dump the database because we didn't have the free space on our server. So I was forced to reboot the box. Then tempdb returned to normal size. I stripped off autogrow, set the data mdf to 500MB and the log to 250MB, and turned Profiler on to see if I could find the source of our problem. At 2pm today the Event log showed nothing exceptional but by 2:30 it indicated that "templog.ldf" was full (Operating System error 112) (There is not enough space on the disk) encountered."
What else should I look? Or is there a totally different approach I should take to track down this problem.
TIA,
Bill
P.S. I thought I sent this help request to the newsgroup but I didn't see it appear at all. Please excuse me if this turns out to be a resend.
February 1, 2006 at 10:25 pm
Hi,
As the Tempdb is in simple recovery, the log should be truncated as soon as it reaches 70%, so probably there are open transactions in the database which cannot be truncated.
Try to find out the open transactions using DBCC opentran(database_name) command.
Also use the SQL profiler to find out which query is taking a long time.
-seemita
February 2, 2006 at 7:45 am
Bill,
Two issues.
1. The TEMPDB is growing huge. There are two usual causes of this. First, you have some huge transaction occuring or are doing database maintenance. Second, you have your autogrow set incorrectly (too large). Solutions, start with the autogrow (as you have done - see next issue). If that does not solve the issue, check to see what you have running during the time the TEMPDB grows.
2. TEMPDB log being full. This can occur with ALL logs that have the AUTOGROW incorrectly set or turned off. If you set your autogrow for too small of an amount, the log will fill before it is able to grow. For example, you run something that creates 25 MB of growth. But you only allow 1 MB of growth at a time. If the process attempts to dump 3 MB, and the only available space is 1 MB, the database cannot grow fast enough (it needs two extra MB of space, but can only get 1 MB per the autogrow).
Setting autogrow is almost a science. Set it too high and you quickly run out of space - this usually occurs when percentage is used. Set it too low, and the log file fills too fast.
You need to 1) find out what process you are running that is expanding TEMPDB, then 2) find out how much space it needs, then 3) set autogrow to accomodate that or set the size of TEMPDB to be that size.
-SQLBill
February 3, 2006 at 5:14 am
It verifies its processes therefore probably you it has some process with high use of temporary space, executes query below "select * from sysobjects where name like '#%' ", and also looks for for processes as type DBCC REINDEX
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply