Sunday, January 09, 2011

The transaction log for database tempdb is full

If you get the error message for Tempdb Transaction log is full.

Msg 9002, Level 17, State 4, Procedure sp_helpdb, Line 19

The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Solution: 1

-- First check Tempdb Tran log file size using  dbcc sqlperf(logspace)

-- tempdb 999.9922 99.99628 0

USE MASTER
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME='templog', SIZE=1500MB)

Solution:2

ALTER DATABASE Tempdb
ADD LOG FILE
( NAME = tempdblog2,
FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf',
SIZE = 10MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB)

5 comments:

Atif said...

Thanks for a helpful blog here,
I would like to add that this error also shows up if you are running low disk space on your server, in that case all you need to do is to release some space on the disk by deleting unnecessary files/drop unused databases.

SQLDBA said...

You are welcome

Sathish said...

I have a doubt.. In other cases our temp DB Drive got full then what we can do???

SQLDBA said...

Either you can delete unnecessory files on that drive or you can add physical drive increase drive space.

SQLDBA said...

Create another tempdb data file on another drive