Error 9002, tempdb log full

We have a reporting database that end users run ad-hoc queries against (SQL
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
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.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
DanDan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is ver
y
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:

> We have a reporting database that end users run ad-hoc queries against (SQ
L
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log fo
r
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she i
s
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of dis
k
> space, and have gotten this message with tempdb log set to unrestricted an
d
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because
it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this
?
> Thanks,
> Dan