• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4852
  • Last Modified:

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

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
JElster
Asked:
JElster
1 Solution
 
BembiCEOCommented:
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
 
JElsterAuthor Commented:
How do I enlarge it?
0
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now