tempdb in sql server

Question

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.

• niktrs: What is the recovery model for tempdb? Normally it should be simple.

Answer 1

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.

Answer 2

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

• Komal: its also same...autogrowth already enabled and unrestricted file growth. Still i am getting that error
• niktrs: Does the machine have enough free space for the log file to grow?
• niktrs: For the moment try restarting SQL server
• niktrs: Can you try: select * from tempdb.sys.database_files and provide us the output?
• Komal: restarted the server...and this query is not working.

Answer 3

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

Answer 4

The following links may help:

http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/

http://web.archive.org/web/20150116150813/https://web.archive.org/web/20080509095429/http://sqlserver2000.databases.aspfaq.com:80/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html