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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 603
  • Last Modified:

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

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.
0
AnHao
Asked:
AnHao
  • 2
1 Solution
 
reb73Commented:
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
0
 
reb73Commented:
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..
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 ...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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