Transaction Log full on TempDB

This post aims to give the readers a simple step-by-step methodology to deal with 'Transaction Log Full' situations. The general perception is that we should not care about transaction logs if the recovery model is set to Simple. But actually that's not always the case. A very common phenomenon happens when the transaction log on the TempDB blows up because of some spid in KILLED/ROLLBACK state. It happens because while rolling back the transaction also the transaction log is used. So let's get started with how to deal will this issue,


1. Do DBCC OpenTran to know which transaction is the actual culprit.
2. Check the spid that you got from step 1 using sp_who2. It will give you an idea of what is actually the reason  behind the issue and if you can kill it without affecting anything important.
3. If you can kill the spid, kill it. This will solve the transaction log issue. If not, see if you can increase the size of the log file using,

USE MASTER

GO

ALTER DATABASE TEMPDB MODIFY FILE (NAME ='templog' , SIZE= 8500MB )

Or add another file for the log,

ALTER DATABASE Tempdb

ADD LOG FILE

( NAME = tempdblog2 ,

FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf' ,

SIZE = 10MB,

MAXSIZE = 100MB ,

FILEGROWTH = 10MB)

4. The above step will solve the problem in most of the cases. But there is a possibility that the disk where the log file is has already grown to full.  in such case either you  can get more space added to the drive by calling your admin or you can shrink some database files of the databases that are lying on that drive.  Once you have more space on the disk you can go back to step 3 to fix the issue.

I hope this simple article will help few DBAs in dealing with this issue. As always your comments are welcome.