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

Posted on 2009-02-14
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.
Question by:AnHao
    LVL 25

    Expert Comment

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

    USE tempdb
    DBCC SHRINKFILE(1, 10240)  -- reduces tempdb.mdf to 10GB
    LVL 25

    Expert Comment

    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..
    LVL 142

    Accepted Solution

    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.

    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 ...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now