TempDB is Full Error
-
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space. Error 9002 Severity 17
I don't understand how this message appears considering:
SQl 2000 SP2 on Windows 2000
Default TempDB settings i.e. auto grow till disk full
1.9GB free space on the drive that holds TempDB
Surely TempDB should continue to grow until approx. 1.9GB.........?
As there was no server restart between this error and myself checking the server I would have expected to see TempDB at its final size - the size that caused the error. It's only 19Mb!
Thanks for any ideas.
-
Run dbcc sqlperf(logspace) to see how large TEMPDB transaction log is and spaces used.
TEMPDB should be in "simple" recovery mode.
You should allocate more spaces for both TEMPDB data and log files. 19MB is too small.
-
TempDB has default settings so it is in simple recovery mode already. Logfile is 4MB (sql has not been restarted since the error.)
I was under the impression that main reason for sizing TempDB upwards was to prevent performance overhead every time autogrow kicks in. Leaving it at a low value should not cause the above error.
-
Not allowing the tempdb to Auto grow is probably the most worst thing to do. Then things start getting really bad for your DB applications.
We had the same problem last week on a Friday. Our tempdb had grown to 17 GB and had filled up the whole database disk. We had to use DBCC SHRINKFILE('tempdev',50) and DBCC SHRINKDATABASE('tempdb',20) to get the file back down to a normal size. On Monday we couldn't even use the DBCC commands to get the file back down.
We had to insert a new HD and move the database to the new disk.
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
Even restarting the server didn't rebuild the database, but using the DBCC commands did.
What we have figured out, is that our Content Management System with all its triggers and sprocs is creating heaps of information for the tempdb. Because our Intranet Users all use the CMS at any given time, the temporary tables used by the sprocs and triggers can't be released in a sensible manner. The temp tables are deleted but the space cannot be freed.
E.g.
- One user navigates through the system and triggers a stored procedure.
- The sproc creates a temporary table
- A second user starts navigating
- The sproc creates a temp table for the second user
- The first user quits the CMS
- The tempdb releases the temporary table for the first user
- Because a second temporary table has been created, the system cannot empty the space used by the first temporary table. The data for the second table is after the first temporary table
The result is that the tempdb is using a large amount of disk space, but actually contains a small amount of data.
Our solution is to create a Job that runs the DBCC command SHRINKFILE and SHRINKDATABAES at off-peak hours.
quote:
TEMPDB should be in "simple" recovery mode.Tempdb can't be set to run in Full Recovery Mode. Try turning it on. You'll just get an error message.
We'll be analyzing our TEMPDB the next few weeks and I'll add more informaiton as it becomes available.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
Beath,
Your TEMPDB is too small in both data and log files. I would suggest to increase them to 200/100MB as starting and monitor the usage for later adjustment.
As John suggested, you could always shrink it.
I just found very informational KB from Microsoft to this question.
http://support.microsoft.com/default.aspx?scid=kb;en-us;315512
Edited by - Allen_Cui on 04/16/2003 12:15:15 PM
-
quote:
TempDB has default settings so it is in simple recovery mode already. Logfile is 4MB (sql has not been restarted since the error.)I was under the impression that main reason for sizing TempDB upwards was to prevent performance overhead every time autogrow kicks in. Leaving it at a low value should not cause the above error.
-
A small initial and growth size can cause the problems you are seeing. If TEMPDB can't grow fast enough you'll see those errors. For example, start with 100 MB size and autogrow by 50 MB. Then have a transaction that requires 5000 MB. You'll get the error about not enough room because you are requiring the TEMPDB to grow 4900 MB in 50 MB chunks. It can't happen quick enough. I've had this happen to me.
-SQLBill
-
I wanted to confirm that we have had a similar problem on one of our databases where the tempdb simply couldn't seem to grow fast enough to handle the query thrown at it. Our fix was to pre-allocate storage for tempdb.
I blogged about it here - entitled "tempdb logfile is full...".
-
I have exactly same error and am trying to solve it. The tempdb.mdf file grown to 5 gb. I run dbcc statements but the file size is still the same. My results of dbcc statements are as below:
dbid fileid currentsize minimumsize usedpages Estimatedpages
2.002.0096.00 64.0096.00 64.00
Any suggestions.
Thanks,
-
The previous posts are correct. If you're getting the 9002 log file full error then you don't have a large enough TempDB log file. Pre-allocation of space is a great idea. I have a longer discussion on my blog at http://spaces.msn.com/members/Dausman/Blog/cns!1pugD9ZQNpvEQDaj87K9usXA!184.entry. Hope this helps.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply