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,201 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:
ScottPletcher 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now