Solved

The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space

Posted on 2014-04-14
3
4,272 Views
Last Modified: 2014-04-16
Can somebody give me a SP I can run weekly to get rid of this
thx

The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space
0
Comment
Question by:JElster
3 Comments
 
LVL 35

Expert Comment

by:Bembi
ID: 40000183
The tempDb is freed up when the server restarts. But if used in a too heavy way there are two solutions. Either to enlarge the size of tempDB or to work with several tempDBs (with the same size). Applications should freeup the tempDB themselves, but it depends what happens on the machine. Some large and heavy queries can force the tempDB to take a lot of space.

The other option is to - as stated - to backup the tempDB logs regularly, what frees up the logs. So if you have a backup software in place you may shorten the intervals for the tempDB. But this makes only sense, if the applications is not overruning it by its queries.
0
 
LVL 1

Author Comment

by:JElster
ID: 40000190
How do I enlarge it?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40000314
You can't back up tempdb, or its log file.  (And you can't have "several tempdbs"; you can, and should, have multiple tempdb data files, but never multiple log files, on any db (except in an emergency).)

You increase the log size with this command:

ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog, SIZE = nnMB, FILEGROWTH = nnMB )

For example, if you want to go to 12GB, this would be the command:

ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog, SIZE = 12000MB, FILEGROWTH = 100MB )


For performance reasons, you should check to see how many VLFs you have on tempdb.  If it's greater than ~200, you should recycle SQL when you can to reset the VLFs.

Or you can shrink the log file to a minimum size and then re-grow it without a recycle, but there is a tiny chance that could cause issues and force you to recycle SQL anyway.


If you don't have room on disk to grow tempdb log file, you have an emergency and could add a second log file for tempdb for SQL to use, then remove that log file later.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question