Log file for database tempdb is full.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Log file for database tempdb is full.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-03 : 09:04:55

John writes "On occasion I receive the following error:
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..

Both the data file and transaction log are set to automatically grow, by 10 percent with unrestricted file growth. The hard drive where these files are being stored has over 8 GB available. I have researched the problem in Books Online and can not find a reason as to why I am receiving the error.

Thank You"

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-05 : 11:59:36

I get this error a few times myself.
This will get rid of your tempDB log if you need (deletes it fully... Would only recommend if this is in a test enviroment)
backup log Tempdb with truncate_only

Are you running large queries on temp tables? What I'd recommend is to breakdown the size of your insert or update statements using where clauses

declare @colid
set ColID = 1000
while coldID < select max(colid) from mytable
begin
begin transaction
select * from mytable
into #temp
where colid < @colid
set colID = ColID + 1000
commit transaction
end

Something along those ends. In one of my tables I have a type ID that contains values 1-50. About 250k entries per type. So I just incremented on my type column.

Commit/begin transaction are the 2 key terms there

-----------------------
Take my advice, I dare ya

Edited by - M.e. on 07/05/2002 12:00:14

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources