|
Home > Archive > Microsoft SQL Server forum > July 2005 > tempdb log full error with plenty of disk space available
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
tempdb log full error with plenty of disk space available
|
|
| New MSSQL DBA 2005-07-05, 3:23 am |
| has anyone met with this before?
the setting is SQL2K with SP3 on a 2 node active-active W2K3 cluster.
on one of the machine, it occasionally prompts for the following error:
"The log file for database "tempdb" is full. Back up the transaction
log for the database to free up some log space."
the problem is, at the time of error, the tempdb tx log is only 200MB
and there are over 50G disk space available.
settings of tempdb:
-- 10% autogrow, unlimited max size
-- auto shrink off
-- data file around 1G
Thanks.
| |
|
| Haven't ever seen that error, but stepping back a bit, it sounds like
you need to resize your tempdb. As you probably know, file I/O is very
expensive for processing power. If your tempdb is growing, then you
should resize it anyway.
That being said, that still doesn't solve your problem; it just delays
it a bit. But by changing the size, maybe some other erro will come to
light.
HTH,
Stu
| |
| New MSSQL DBA 2005-07-05, 3:23 am |
| I've found nothing in Microsoft's KB but found that some ppl seems to
have similar problem on other sql server forum.
Their explanation is that the TEMPDB is simple not large enough and
fast enough to handle a sudden query request of large temp space for
say sorting.
Their solution seems to increase the TEMPDB size to a larger size.
Further that, these errors occur after the server upgrade (from W2K to
W2K3, CPU 4x --> 8x, RAM 4G --> 12G).
Anybody has more input? Thanks
| |
| Mike Epprecht \(SQL MVP\) 2005-07-05, 8:23 pm |
| Hi
Adding the RAM probably increased performance, by adding more concurrent
query support, thereby pushing your existing TempDB size past it's limits.
Grow your TempDB to a size that is big enough.
Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"New MSSQL DBA" <boscong88@gmail.com> wrote in message
news:1120543875.680342.166850@o13g2000cwo.googlegroups.com...
> I've found nothing in Microsoft's KB but found that some ppl seems to
> have similar problem on other sql server forum.
>
> Their explanation is that the TEMPDB is simple not large enough and
> fast enough to handle a sudden query request of large temp space for
> say sorting.
>
> Their solution seems to increase the TEMPDB size to a larger size.
>
> Further that, these errors occur after the server upgrade (from W2K to
> W2K3, CPU 4x --> 8x, RAM 4G --> 12G).
>
> Anybody has more input? Thanks
>
|
|
|
|
|