October 20, 2008 at 12:40 pm
I have a SS2000 SP4 running a helpdesk application. Today I found this message in the log:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
The .ldf file is set to autgrow at 15mb at a time and it was only consuming 20mb of space. Autoshink is not set on the db properties nor is it scheduled.
The drive that temp mdf and ldf files are on has 2 gig of free space...
Any ideas? I did run this that I found doing some research.
backup transaction tempdb with truncate_only
go
checkpoint
go
October 20, 2008 at 1:57 pm
Is there a value for the maximum file size?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2008 at 2:03 pm
No there is not. That is what is puzzling !
October 20, 2008 at 8:38 pm
If you only get 1 9002 message my guess is the transaction is failing on the full log and rolled back. Typically in Sql2k if the tempdb log fills a restart of the Sql Server service is necessary so rollback probably saving you from locking up your instance.
Even for a helpdesk app, your tempdb is small and letting autogrow manage your space is a bad idea.
I would allocate a minimum of 64 meg for a the log and 128 for the data file. Tempdb is not a place to be stingy with space.
Hope this helps.
David
October 20, 2008 at 10:23 pm
Did you check tempdb.mdf?
October 21, 2008 at 5:22 am
The MDF file is 2.3 gig. I manually increased the ldf file to 500mg. What I don't get is it is set to auto grow and there is 2 gig free on the drive. This is not a heavy transaction SQL Server.
October 21, 2008 at 8:37 am
They process probably died while it was waiting for the file to grow. It probably just came down to disk contention.
October 21, 2008 at 8:43 am
Interesting thought. I have never seen this error message before in any of our many SQL Servers over the years. In researching this error it is interesting that very little is posted about why this happens and what the fix is unlike other SQL Server issues.
October 21, 2008 at 8:58 am
This does sound strange and I don't think this happens often. It's likely an anomaly as mentioned above. If it happens again, I'd recommend calling MS, running PSdiag, and posting an update here. Be curious to know what this is.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 21, 2008 at 9:00 am
Will do. Thanks to all !
October 21, 2008 at 9:10 am
There was a wellknown bug on SQL 2000 related to tempdb percentage auto growth.
October 21, 2008 at 9:15 am
I saw that. I have never liked percentage growth increments. Too small at the beginning and too big when it file is large. From day one I change all dbs to grow by a specific MB. THanks though.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply