Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
btrimpop
Posting Yak Master
214 Posts |
 Posted - 2006-11-21 : 11:11:50
|
| Hi All,I'm getting the following error" Error: 9002, Severity: 17, State: 6 The log filefor database 'tempdb' is full. Back up the transaction log for the database to free up some log space. and before I get the standard "backup trx log, check disk space, etc...I have. It's a SQL 2000 w/ SP 4, Full Recovery Model, all db's are set to Auto grow by 10%. The tempdb is 52 meg, the database in question is currently 800 meg with 600 meg used, the database log file is 400 meg with 200 meg used, there's 103gb free space on the drive. From running the profiler the app (Delphi coded using BDE to connect to db) is doing a pretty simple select that in query analyzer returns 147k rows.Any thoughts?Thanksbtrimpop"In theory there is no difference between theory and practice. But in practice there is!" |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-21 : 12:33:03
|
| "all db's are set to Auto grow by 10%."Including the Log for TEMPDB presumably? The error is indicating that TEMPDB is full, and that suggests its set to a fixed size - given that you have plenty of free disk space - unless its on a different drive, that has no free space ?Sorry to start with the mundanely simple, but worth checking that first I reckon!Kristen |
 |
|
|
btrimpop
Posting Yak Master
214 Posts |
Posted - 2006-11-21 : 12:44:41
|
quote:
Originally posted by Kristen
"all db's are set to Auto grow by 10%."Including the Log for TEMPDB presumably? The error is indicating that TEMPDB is full, and that suggests its set to a fixed size - given that you have plenty of free disk space - unless its on a different drive, that has no free space ?Sorry to start with the mundanely simple, but worth checking that first I reckon!Kristen
Hi Kristen,Yeah, including the log. All the files are on the same drive. All db's and logs are set to auto-grow. This one has me stumped. There are actually 2 databases (for 2 different applications) on this server. The one database has very large batch processing routines that run on it routinely and cause the tempdb and transaction logs to grow pretty big (20 gig range), but this db does not generate any errors. The other db does not have nearly the volume or traffic or size and yet running simple processes (selects, inserts, updates) the tempdb error occurs at seemingly random. Sometimes it errors, sometimes it doesn't.There's a scheduled maintenance job that runs hourly to backup the transaction logs. Very frustrating!"In theory there is no difference between theory and practice. But in practice there is!" |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-11-21 : 13:02:06
|
| I believe you can not set the tempdb to full recovery. I have encountered a bug in SQL Server (not sure if it has a Q article, or not) where if you cancel a large query error 9002 is generated. I have never gotten so deep into the particulars that I have figured out what about the cancelled queries is so special. It may be the sorting, or the grouping, but all cases they were querying large amounts of data. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-21 : 13:19:39
|
| "I believe you can not set the tempdb to full recovery"I read that and assumed it didn't include TEMPDB - nor MASTER for that matter! But worth double checking ...No point backup TEMPDB up either, is there?I remember seeing this come up before, but I can't remember the upshot. If my memory is correct you might be able to hit on it with Google though - and the only place I am likely to have seen it is here. (Constraint google with "site:sqlteam.com" perhaps?)Can you reproduce the problem easily?Stop/Start SQL Server should throw away TEMPDB and start over. Still reproducible?Kristen |
|
|
|
btrimpop
Posting Yak Master
214 Posts |
Posted - 2006-11-21 : 13:57:01
|
| Sorry, wasn't clear on that. Master and Tempdb are set to Simple, the application database is set to full with and hourly transaction log backup maintenance schedule. Master and tempdb data and log files are set to auto-grow by 10%.The application database data and log files are set to auto-grow by 10%.Yes the problem is reproducible fairly regularly (it happens as often as not). We've restarted the server several times and ot doesn't really seem to impact whether the problem occurs or not.Thanks"In theory there is no difference between theory and practice. But in practice there is!" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-21 : 14:11:49
|
| I wonder if TEMPDB can't grow quickly enough. I'm dead against percentages for file expansion. 10% for TEMPDB at around 50MB is only a 5MB expansion - and its probably been expanding from 1MB initial size in 10% increments.Maybe try setting its start size to, say, 500MB and a 100MB expansion?Personally I'd do the same for your databases. We had problems with user sessions timing out when the database expanded, which were fixed by changing from 10% to a more manageable number of MB instead - particularly on databases which were more than several GB large.Kristen |
|
|
|
btrimpop
Posting Yak Master
214 Posts |
Posted - 2006-11-21 : 14:18:50
|
Hi Kristen,Worth a try I guess, but doesn't explain why a much larger db with a much larger transaction volume on the same server with the same db settings does not produce the error? Still very frustrating..  "In theory there is no difference between theory and practice. But in practice there is!" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-21 : 15:14:14
|
| "doesn't explain why ..."Well, I'm guessing too .. but different (less) contention for TEMPDB maybe?Kristen |
|
|
|
|
|
|
|
|
tempdb full problem