tempdb log full error with plenty of disk space available

[New MSSQL DBA]

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.

[Stu]

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]

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\)]

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: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"New MSSQL DBA" <bo*******@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.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