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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

migrated from Jan 22 '12 at 13:02

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

What is the recovery model for tempdb? Normally it should be simple. – niktrs Jul 5 '11 at 7:00
up vote 4 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.


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.

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


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.

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):


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
@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
may be thats happening because of the temporary table being created in the procedure referred. – Komal Jul 6 '11 at 13:05

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

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

If your DB is 2005 or 2000 you can use this :

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

USE DatabaseName

Hope that helps, Dave

share|improve this answer

The following links may help:

share|improve this answer
While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Derek Downey Dec 16 '15 at 14:38

Your Answer


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