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,
Dan
Dan,
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 very
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 (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,
> Dan