Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
Medium Priority
?
4,705 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Integration Management Part 2
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 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