log file for database 'tempdb' is full. Back up the transaction log fo 1

[hoggle]

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

I read the other posts on this message board concerning this, thread183-355903 and I changed my log file for tempdb to grow at 50%, and I still get this error,
I looked at free disk space, and the tempdb sits on a drive with 12gigs free and orginal tables are only 1 gig in size, so it seems like that isn't the problem.

the error comes when I do large query on a table that contains 313173 records, but if I have the disk space shouldn't it let me run the query?

any help at all will be appreciated, because I am at a loss.
thanks everyone

 

[ecobb]

You probably need to truncate your Transaction Log file. SQL Server should automatically do this when you do a transaction log backup.

Hope This Helps.

 

[RiazKhanmohamed]

the transaction log is slightly different, and not related to hard disk space. i believe you can set size, but it depends how much log space you are allowing - this can be set under properties of the database - there's a tab called transaction log. simply set size and location.

alternatively check this recent thread for more on the same:

http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/27/pid/183

 

[rdl1206]

hoggle, Make sure that you have all your sysdatabases in your maintenance plan. When you are sure you have a good database and transaction log backup, you can run...

BACKUP LOG tempdb WITH TRUNCATE_ONLY

in Query Analyzer.

 

[SQLBill]

50% is a huge amount to grow any database by. Even though it allows the database to grow fast, it can cause it to outgrow the disk space.

First, check what you are allowing the TEMPDB to grow to. In Enterprise Manager, right click on TEMPDB, select Properties, go to the Data Files tab and look what you have set for Maximum growth. You might be out-growing that space.

Next, on the same page in the window for Data Files, how much space is ALLOCATED to the tempdb?

That's what FILE GROWTH goes by when it expands.

Three reasons for the problem you are seeing;
1. not allowing the database to grow fast enough
-this is why I set mine for MB not %. I can control it better

2. Max growth size isn't large enough

3. database space expanded so much that it won't be able to expand again.

Here's an example of #3:
database set for 50% growth, allocated size is 5GB, disk space is 15Gb

First growth: from 5 GB to 7.5GB
Second growth: from 7.5 to 11GB
Third growth: from 11 GB to oopps not enough room, this is where you would see the error.

Even if you didn't need all the room on the third growth, SQL doesn't know that. IT needs all that room to be there even if it really only needed 1 GB on the last growth.

-SQLBill

 

[hoggle]

Great information everyone, I figured out that my query was doing a type cast, because I'm using a CASE statment, as soon as I changed the query to not include the case statement and lower the log growth to 25%, it seems to work.

Everyone gave me great information.
Thanks again

 

[eja2000]

what are the implications of using WITH TRUNCATE_ONLY ?
will i still be able to restore the db together with full and differential bkps if i run a log bkp with the WITH TRUNCATE_ONLY command every hour?

 

[SQLBill]

TRUNCATE_ONLY doesn't backup the LOG file. It just truncates the data. It won't affect the FULL or Differential backups nor the restores. You will NOT be able to restore to a 'point-in-time' since you need log backups to do that.

If you aren't going to backup the logs, change your Recovery Mode to SIMPLE.

-SQLBill

Preposting reading: FAQ481-4875, FAQ222-2244