TempDB Space Issue

TempDB Space Issue

You may have come across situations where the TempDB has grown very large, sometimes even completely running out of space.  When this happens, you need to shrink the TempDB.

DBCC SHRINKFILE ('tempdev', 1024)

A couple of problems sometimes comes up after doing this:
1. The TempDB shrinks successfully.  However, there is still a process running out there that will fill up TempDB again.
2. The TempDB does not shrink.

For the 1st issue, use the following query to check how much space is being used in TempDB.


SELECT
[TotalSizeOfTempDB (MB)] = 
SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count
+ unallocated_extent_page_count) * (8.0/1024),
[UsedSpace (MB)] = 
SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count) * (8.0/1024),
[FreeSpace (MB)] = 
SUM(unallocated_extent_page_count * (8.0/1024))
FROM sys.dm_db_file_space_usage
WHERE database_id = 2


Most likely, you will see the a large amount of used space, or the used space continue to grow.  To find the culpable transaction, run the following command, which will give you the SPID for the oldest open transaction.

DBCC OPENTRAN('tempdb')

With this SPID, you can now run the following to find the SQL command being executed as well as the user and machine executing it.

DBCC INPUTBUFFER (<SPID>)
SP_WHO2 <SPID>

From here, it should be easy to track down the person/process responsible and take the appropriate action to rectify.  It may be to stop a job on the front end application, re-write the SQL command to be more optimal, or even just kill the SPID.


For the 2nd issue, you may be unable to shrink TempDB.  The reason TempDB cannot be shrunk may be because there are uncommitted transactions or the proc/system cache needs to be cleared.  Re-starting the SQL Server instance will shrink the TempDB.  However, this is not always an option, especially in a production environment.  To get around having to restart the SQL instance, run the following to shrink TempDB.

USE tempdb
GO
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE ('tempdev', 1024)
GO

HOW TO MOVE TEMP DB IN SQL SERVER 2005/2008 R2

All temporary results from stored procedures of all databases are temporary stored in the tempdb. Because of its central part in the execution of T-SQL statements it can easily become a performance bottlekneck. To mitigate this it is best to move the tempdb to a fast drive instead of its default location in c:\. This is especially true for a SharePoint database server. All uploaded documents go through the tempdb before being put into the content database.

See also article: How to optimze temp db in SQL Server 2005/2008 R2 by splitting the database in multiple files

Steps:

1. Determine the logical file names for the tempdb database:

use tempdb go sp_helpfile go

The logical name for each file is contained in the name column.

2. Move the temp db using the ALTER DATABASE statement, specifying the logical file name as follows:

use master go  alter database tempdb modify file (name = tempdev, filename = 'E:\MyFolder\tempdb.mdf') go  alter database tempdb modify file (name = templog, filename = 'E:\MyFolder\templog.ldf') go

3. Stop and then restart SQL Server.

SQL TIPS: SQL Tempdb log is full

16AUG

For those of you who have gotten here with a real issue, here is the fix. If you run this, your server will probably start to cook until the bad tempdb eating query is finished.

USE [master] GO ALTER DATABASE [tempdb] ADD LOG FILE ( NAME = N'templog2', FILENAME = N'C:dbtemplog2.ldf' , SIZE = 131072KB , FILEGROWTH = 131072KB ) GO.

Now, we can get into the why part. First, a little about tempdb. This system database is used by sql server for many behind the scenes processes. It is truncated every time sql starts. Its proper configuration is vitally important to performance. tempdb is in the simple recovery model and cannot be changed. If your server crashed you would not restore tempdb so there isn’t a need to back it up.

Simple mode databases overwrite previous log entries once a checkpoint occurs. Linchi Shea points out that huge long running transactions are more likely to fill up a simple log but it is possible to do it with fast enough small transactions. http://sqlblog.com/blogs/linchi_shea/archive/2009/05/21/why-does-my-tempdb-log-keep-growing.aspx

When I ran into this problem, I saw a server that had a bunch of open connections that were waiting on something to actually do some work. No CPU or Disk was active at the time. The SSMS GUI was working slightly but no agent was showing and I would get errors when I tried to open certain features. The one thing I could do is run queries. The problem was shown when I first connected to the server. Now I just needed to fix it and find out why it happened, maybe not in that order.

The first few things that poped up on google are not good solutions or even solutions at all.

1. Backup tempdb log – nope can’t do this because its in simple mode.
2. Backup tempdb log with truncate_only – nope again
3. Shrink the log – bad idea, beside there is no free space…
4. restart sql

Technically, 4 would probably work. However, its a bad idea because you then hinder your troubleshooting abilities. There was probably one bad query or report that once it fails, the user will just try again. If you were in the middle of something, when your server comes back online it might be stuck “in recovery…” Before you grow the log you should try to fire off sp_whoisactivehttp://sqlblog.com/blogs/adam_machanic/archive/2009/12/03/who-is-active-v9-57-fast-comprehensive-dmv-collection.aspx That will show you active sessions, the login, the query and their tempdb activity, all the ammunition you need to stop this problem dead in its tracks.