Temp DB log full

[cyno]

Hi Guys - I have question on temp DB. Last night we have huge updates on one of the user databases. I know these updates will use the tempdb space. I got an alert saying that the log file in the Temp DB database is full. Please back up the T log to free up some space. But the temp DB log file is set to autogrow with unrestricted file growth specified.

I didnt understand why i get this error msg when it is set to autogrow. I am thinking that the file cannot grow automaticaly until and unless a transaction is commited. Any help is much appreciated.

Cyno

 

[cyno]

There is enough disk space (150 GB) free.

 

[Catadmin]

The Temp DB shouldn't need a transaction log file. Only your user DBs should need one. The Temp DB is only used for inserts, updates and deletes and then "purges" the data as necessary.

Check the properties of your TempDB to see what Recovery Mode it is set at (Full, Bulk-Logged or Simple). It should be set to simple. If not, truncate the log for that database and set it to simple.

If it is, double check your error message to see that it isn't talking about the Transaction Log on a specific user db. Post the error message here if what I told you to do doesn't help.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[cyno]

The tempdb is in simple recovery mode. And here is the error message i see in the error log

Description: Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.

Actually iam doing some inserts and updates on a user DB which is 60 GB.

Cyno

 

[JamesLean]

If you have a particularly large update it may be that the log file cannot grow quickly enough to handle the extra data.

Try temporarily increasing the log file size manually to something large, do the update and then shrink the log.

--James

 

[Catadmin]

<nod,nod>

What James said. You can actually kick up the percentage or the # of megabytes the log will grow by via the Enterprise Manager database properties OR use a T-SQL Alter Log statement in Query Analyzer to do an actual "now" size increase.

On the other hand, given that TempDB is in Simple mode, it really shouldn't be using a Transaction Log, so I would check the log growth increments on your User db also.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[cyno]

Thanks for your quick response.

So if we are doing huge updates or inserts do we need to increase the Log file of Temp db to make sure it doesnt run out of scae???

I already have the autogrow option on for data and log file for the tempdb.

Cyno

 

[JamesLean]

Catadmin, the simple recovery model does not mean that a database does not have a transaction log. It just means that you do not need to take tlog backups and it is truncated automatically on checkpoint.

--James

 

[cyno]

Ok got it. Thanks a lot

 

[Catadmin]

Actually, you need to go to Books Online and do a little reading.

Simple Recovery Mode means the database *doesn't* use a Transaction Log. If you are doing huge updates or inserts on a User DB, I would recommend backing up the user db (a complete backup, not differential or Transaction log), changing the User DB recovery mode to Bulk Logged or Simple, doing your updates or inserts, backing up the user db again (another complete backup), then changing the recovery mode back to Full on the user db.

The error message you received is very generic. Don't take the DB name there literally. Your problem is very much with the database you are making changes on.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[Catadmin]

Sorry, Cyno. I didn't mean that last post to come out so harsh. I get aggrevated with Microsoft's half meaningless error messages sometimes. They've sent me on a number of wild goose chases that end up on something completely different than what the error message originally said.

BLARGH! I swear I'm gonna send the Buffy finger puppet monsters after the guys who write these things. @=)

Still, look up Simple Recovery, Full Recovery and Bulk Logged Recovery in Books Online and you should get an idea of what I'm talking about. Also look up the TempDB so you can understand exactly what it does.

Hope that helps.





Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[JamesLean]

Sorry to labour the point but where exactly did you read that "Simple Recovery Mode means the database *doesn't* use a Transaction Log"?

Simple Recovery requires the least administration. In the Simple Recovery model, data is recoverable only to the most recent full database or differential backup. Transaction log backups are not used, and minimal transaction log space is used. After the log space is no longer needed for recovery from server failure, it is reused.

As I said, T-log backups are not used but the database still has, and uses, a T-log.

--James

 

[Catadmin]

Hmm. Probably right where it says "Transaction log backups are not used" and I probably skimmed over the "backup" part of that sentence. @=/ It's been so long that I honestly don't remember.

Sorry, my bad. Can I use Friday as an excuse for drain bramage?

Regardless, I still advise changing the user DB recovery mode during these updates and inserts and doing backups before and after. If I'm not mistaken, this can even be written up as a T-SQL query which can be scheduled.





Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[cyno]

James i agree with you.If the DB changes are from Application side (Front end). I dont think changing the User DB mode a good option. Anyways its a one time process in my case. Thanks to you both for your help.

Cyno

 

[SQLBill]

cyno,

I have had this happen to me, especially the first time I did a DBREINDEX.

My TEMPDB files are also set to autogrow. Problem is I didn't set them to autogrow LARGE enough for what was needed. The TEMPDB log would run out of room and start to grow, but that room was already to small for the transactions happening.

I haven't found a hard and fast rule as to what to set the growth as, you'll have to find out what works for you. You could try setting autogrow to 2000 for when you expect large growths.

-SQLBill

Posting advice: FAQ481-4875