TempDB Full – How to Clear TempDB Log in SQL Server 2005

The SQL Server log proves that tempdb was having problems…

02/19/2009 11:50:41,spid278,Unknown,The transaction log for database ‘tempdb’ is full. To find out why space in the log cannot be reused<c/> see the log_reuse_wait_desc column in sys.databases
02/19/2009 11:50:41,spid278,Unknown,Error: 9002<c/> Severity: 17<c/> State: 4.


Do the following to find out why tempdb is not reusing space – the Log_reuse_wait_desc will give details.

Select [name], log_reuse_wait_desc from sys.databases where name = ‘tempdb’

The most common reason for this is a long running, active transaction. The log_reuse_desc will be ACTIVE_TRANSACTION if this is the case. If so, you can run the queries below to find the culprit:

-- Find oldest transaction
DBCC OPENTRAN
-- Get input buffer for a SPID
DBCC INPUTBUFFER(21) – Substitute the SPID number from above

In future, If Tempdb fills up then it is not necessary to restart SQL Server. The following actions should be taken:

On the SQL Server – Open Query analyser and run:

–This will show the size of the tempdb

Use [Tempdb]

GO

SELECT name AS ‘File Name’ , physical_name AS ‘Physical Name’, size/128 AS ‘Total Size in MB’, size/128.0 CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’—, * FROM sys.database_files;
 

–This will shrink all segments up until the last used segment – so any free segments at the back of the tempdb data file

USE [tempdb]
GO
DBCC SHRINKFILE (N‘tempdev’ , 0, TRUNCATEONLY)
GO

–To find out which segments are in use etc – a status of 2 shows that the virtual segment is in use
use [tempdb]
go
dbcc loginfo
go

If there are empty segments then you can run a script to rearrange segment usage – The number variable below is the size you want to shrink to

USE [tempdb]
GO
DBCC SHRINKFILE (N‘tempdev’ , 1024)
GO 

Tempdb will not release segments if they are in use by open transactions. Please read this before attempting any Tempdb alterations http://support.microsoft.com/kb/307487

Advertisements

4 Responses to TempDB Full – How to Clear TempDB Log in SQL Server 2005

  1. danieladeniji says:

    Thanks – Well written.
    I am not sure quite sure whether it is going to get me out of my current TempDB problem. But, I like your approach.

    All the best,
    Daniel

  2. satya says:

    hi thank you for the information

  3. Pingback: Fix How To Check Error Log In Sql Server 2005 Windows XP, Vista, 7, 8 [Solved]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: