tempDB is full

Posted on 2009-02-19
Last Modified: 2012-05-06
When i execute queries sometimes on Management Studio i get the following error message

Msg 9002, Level 17, State 6, Line 1
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.

What is this to do with..

What type of repercussions will it have on the DB.. Is it connected to anything like slowness etc


Question by:justin_smith
    LVL 15

    Assisted Solution

    Your query might be generating big temp table which has to go through the tempdb log file and your log file size not enough to hold it so it is running out of...
    either you need to increase the size...if you dont have the space on the existing drive then move the tempdb to another drive...
    LVL 51

    Accepted Solution

    tempdb is used for a few different things, it is minimally logged so rollbacks and log files can be kept reasonably clean, and it is reestablished each time you start SQL Server. So what goes into it ?

    Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
    Internal objects that are created by the SQL Server 2005 Database Engine, for example, work tables to store intermediate results for spools or sorting.
    Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
    Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

    so, what ever you are running at the time, obviously fails... and apart from that, everything else should be OK.

    Now the reason why it is full, is one of two thing - 2nd thing first is it has reached it's capacity of 2 terabytes - unlikely. First and always the reason is that it has run out of available disk - you can watch disk space go down until all consumed.

    Often it is because it is on the default drive when installed and can be competing with system files and other temp work areas. Best thing to do is to move it. Needs to be regarded much like the other log files and held seperately from the data files for the database (nb tempdb.mdf and .ldf can live together).

    You can do things to help improve, or reduce the likelyhood  of tempdb problem, but that is another story.

    The links given above will tell you how to move it.

    Author Comment


    //so, what ever you are running at the time, obviously fails... and apart from that, everything else should be OK.

    I understand the "fails" when i get the error message. But the result are shown though .. the queries i am talking simple selects nothing to do with cursors, temp tables etc in addition to what you listed..

    We have a lot C/C++ applications that do a lot of select queries .. What does this mean to them when these types of error occur... Are their functions that depend on the resultsets returned affected?


    LVL 51

    Assisted Solution

    by:Mark Wills
    Well, depending on what they are doing,  they might not get a resultset... If they are getting a partial result set, then yes, it will be compromised in some way.

    It is definitely a problem, and definitely needs to be fixed, but it what I was meaning about being OK is it would not have compromised the underlying data tables - that I is part of the sacrificial nature of tempdb. It could however, and quite right to question me on this, return a partial result set that might cause erroneous results elsewhere.

    Even a simple select can be something like an order by, or a subselection - which sql server might be doing without you exlicitly knowing, or an index set up to sort in tempdb...

    There might be other jobs or functions contributing at the time, or, you have very little available space....


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now