One Command Keeps Causing "'tempdb' is full" Error

[Jay Chan]

I keep getting the following error message when I run a serie of SQL
commands:

Server: Msg 9002, Level 17, State 6, Line 15
The log file for database 'tempdb' is full.
Back up the transaction log for the database
to free up some log space.

I have tried "dump transaction tempdb with no_log" right before I run
the SQL command. But that doesn't help.

The serie of SQL commands that I try to run is the following:

create table #NewBatOp
(
BatchJournalID uniqueidentifie r not null,
batch_nr varchar(5) null,
OperationNum varchar(3) null,
OperationHours real null,
EmployeeNum varchar(6) null,
OperationDate datetime null,
IsOverTime tinyint null
)
-- |-- Comment this one line
-- | out will not trigger
insert into #NewBatOp -- <---| the error
select
bj.BatchJournal ID, bj.batch_nr, bo.opno,
bo.hrs, bo.bonno, bo.dat, bo.otflg
from batop bo
inner join BatchJournal bj on
bo.bat = bj.batch_nr and
bj.BatchJournal ID in
(select BatchJournalID from BatchControl)
if ( @@error <> 0 )
goto OnError

drop table #NewBatOp
goto EndTest

OnError:
drop table #NewBatOp
print "Error: Failed to import new batch-operations into
journal."

EndTest:

I have tried running the above statements in ISQL and in Query
Analyzer, and I get the same error.

I didn't have this problem before I have moved the database from one
server to another server.
- The OS in the old server is Windows-NT,
and the SQL Server in the old server is the 2000 version.
- The OS in the new server is Windows-2000,
and the SQL Server in the old server is the 2000 version.

The settings in tempdb in both servers are more or less the same.
Actually, the tempdb in the new server is actually much bigger than
the one in the old server. The size of the transaction logs in both
server are the same (and cannot be changed manually). Both the data
and the transaction log of tempdb can automatically grow in 10%
increment and no restriction on size.

The data-and-log of the tempdb are both in one hard disk. The hard
disk has 10-GB free space available. Moreover the size of the result
set from the "select" statement above is only 530KB (around 3000 rows
in the result-set). I believe it is a very small database operation.
Therefore, I don't think the size has anything to do with the error.

I don't think the "inner-join" clause is the cause of the problem. The
reason is that I have used the same "inner-join" clause in other
queries, and they don't have any problem. As a matter of fact, I have
used many other queries that are far more complicated and have created
much bigger result set in tempdb, and they don't have this problem.

I am very puzzled of this error. Can someone give me a pointer?

Thanks in advance for any info.

Jay Chan

[Jay Chan]

> You start with 10 MB. Then it grows by 10% to 11 MB. Then it grows by

10% to 12.1 MB. After 100 autogrows, the log file is 1584 MB. If at
this stage there is not 158 MB free when the next autogrow sets in,
the autogrow fails and you get the message. But say that at this point
the operation was almost done, and all you needed was 20 more MB.
Then you get through with 10 MB increments.

Thanks for pointing out one more time the benefit of increasing the
log file by 10MB instead of by 10%.

But this doesn't explain the reason why I got that "log file of tempdb
is full" error message because I have at least 1-GB free when the
INSERT statement should only take 1 to 3 MB (at most). Therefore,
using up all the available disk space was not the cause of my original
problem.

Jay Chan