We help IT Professionals succeed at work.

The log file for database 'tempdb" is full. Backup the transaction log for the database to free up. Error 9002, sererity 17, state 6.

AnHao
AnHao asked
on
Medium Priority
631 Views
Last Modified: 2012-05-06
My database is SQL server 2000. My database size is 27GB. My tempdb size is 100MB including tempdb.mdf=98MB and tempdb.ldf=3MB). Both tempdb.mdf and tempdb.ldf files are auto grow by 10% with unrestricted size. The free hard disk is 12GB. Users can not query. Please give me your advice.
Comment
Watch Question

Commented:
When you can get all users out, run the following commands -

USE tempdb
BACKUP TRANSACTION tempdb WITH TRUNCATE_ONLY
CHECKPOINT
DBCC SHRINKFILE(1, 10240)  -- reduces tempdb.mdf to 10GB

Commented:
It would be advisable to run these statements in a scheduled SQL job every weekend to keep the tempdb size down on an ongoing basis..
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
reb73:
tempdb is simple recovery mode, you cannot do BACKUP TRANSACTION on tempdb ...
also, doing a shrinkfile on tempdb is not really good action for regular basis... as it will need to grow again.

AnHao:
you will need to identify the reason of why the tempdb needs so much space that it runs out of space.
note: if you get that message, it actually means that you are requiring tempdb space faster than sql server can allocate it (by growing the files of tempdb)

so, instead of trying to cure the symptoms, solve the underlying problems, which are:
* non-indexed queries with group by and order by
* unnecessary temp tables
* long lasting cursor queries
* etc ...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.