Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Using SQL Server 2000, I am getting 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."

How to solve it? I am unable to take backup of tempdb.

I have tried all the options like :

  1. backup log tempdb with truncate_only
  2. dump tran tempdb with no_log;
  3. shrink the database.
share|improve this question
    
What is the recovery model for tempdb? Normally it should be simple. –  niktrs Jul 5 '11 at 7:00
add comment

migrated from stackoverflow.com Jan 22 '12 at 13:02

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 2 down vote accepted

You may want to consider just setting the Recovery Model for tempdb to Simple. That should get you around the problem you are having as it will reclaim all of the space used in the transaction log for tempdb. The MSDN documentation also suggests setting the Recovery Model for tempdb to Simple for performance reasons.

MSDN: http://msdn.microsoft.com/en-us/library/ms175527.aspx

EDIT
According to the documentation, for SQL Server 2005 - 2008 R2, tempdb cannot be backed up and should always be set to the Simple recovery model. If for some reason you are not using the Simple recovery model for tempdb, I would suggest switching to it.

EDIT 2
Just to be certain that the recovery model for tempdb is set to Simple, execute the following stored procedure:

sp_helpdb

Check the results for tempdb, and make sure the status column for tempdb shows something like:

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, ...

If it does, then your problem may be related to the operations you are performing. Are you trying to transactionally write to a temp table? Do you have open transactions that are not getting closed? Is there one operation in particular that is crashing, or does everything not work? You may want to check the answers to this similar question on serverfault for some more ideas. Long story short, if you're still getting this error after setting the Recovery Model to Simple I would start looking at the operations you're trying to perform as a possible culprit.

EDIT 3
From the output of DBCC SQLPERF (LOGSPACE) it appears that your tempdb log file is only 1.24 MB, which seems unnecessarily small to me. You can increase the size of the log file by using Enterprise Manager, or you should be able to use the following SQL (although you might need to look up the log file name by checking the files on tempdb):

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 50MB)

You could try increasing the log file size to see if that helps.

share|improve this answer
    
+1. I guess you are right, recover mode is set to full instead of simple –  niktrs Jul 5 '11 at 7:02
1  
@niktrs I'm wondering if there is ever a legitimate reason to set the recovery model for tempdb to anything other than Simple. I think for 99% of the use cases out there, simple is fine. –  rsbarro Jul 5 '11 at 7:12
    
I guess the only way to do this is accidentaly. –  niktrs Jul 5 '11 at 7:14
    
The recovery model is Simple already. –  Komal Jul 5 '11 at 7:28
    
what to do now ? Stuck on this since yesterday :( –  Komal Jul 5 '11 at 7:35
show 16 more comments

If your DB is 2005 or 2000 you can use this : http://www.codeproject.com/KB/database/ShrinkingSQLServerTransLo.aspx

If it's 2008, you can use the following statements :

USE DatabaseName
GO
ALTER DATABASE [DBName] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([DBName_log], 1)
ALTER DATABASE [DBName] SET RECOVERY FULL WITH NO_WAIT
GO

Hope that helps, Dave

share|improve this answer
    
thank u for the link :) –  Komal Jul 8 '11 at 12:40
add comment

Since your recovery model is simple, check your tempdb log file autogrowth. Set it to unrestricted file growth. Also make sure "Enable autogrowth" is checked

There is a microsoft article

http://support.microsoft.com/kb/307487

Also check if restarting SQL server solves your problem for the moment

share|improve this answer
    
its also same...autogrowth already enabled and unrestricted file growth. Still i am getting that error –  Komal Jul 5 '11 at 8:48
    
Does the machine have enough free space for the log file to grow? –  niktrs Jul 5 '11 at 9:01
    
For the moment try restarting SQL server –  niktrs Jul 5 '11 at 9:02
    
Can you try: select * from tempdb.sys.database_files and provide us the output? –  niktrs Jul 5 '11 at 9:06
    
restarted the server...and this query is not working. –  Komal Jul 5 '11 at 13:28
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.