tempDB is full

Posted on 2009-02-19
Medium Priority
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
  • 2
LVL 15

Assisted Solution

MohammedU earned 400 total points
ID: 23687454
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

Mark Wills earned 1600 total points
ID: 23687791
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

ID: 23688164

//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
Mark Wills earned 1600 total points
ID: 23688313
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....


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

862 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