TEMPDB LOG FULL

Constantly getting this error on large queries.
Have tried to truncate every 4 hours.
Log is set autogrowth

any ideas?
LVL 1
JElsterAsked:
Who is Participating?
 
mebaby333Connect With a Mentor It AdminCommented:
Microsoft also recommends to create multiple tempdb files based on processors available on the server ... http://zarez.net/?p=2130

One tempdb per core on your processor up to 8 cores. there is a bottleneck issue otherwise.
0
 
MacroShadowCommented:
I don't know much in terms of SQL server, but perhaps you will find what your after in this MS article Troubleshooting Insufficient Disk Space in tempdb.
0
 
mebaby333It AdminCommented:
Is it set to simple recovery mode? I believe this will automatically truncate...

ALTER DATABASE tempdb SET RECOVERY SIMPLE
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
mebaby333It AdminCommented:
Is it running out of disk space?
0
 
JElsterAuthor Commented:
Option 'RECOVERY' cannot be set in database 'TEMPDB'.

plenty of space...
0
 
mebaby333It AdminCommented:
are you  logged in with raised credentials? Do you have permission to change settings?
0
 
JElsterAuthor Commented:
yes
0
 
mebaby333It AdminCommented:
In SQL Enterprise Manager, right click on the database then choose properties and then Option  and Recovery change from "Full" to "Simple"
0
 
JElsterAuthor Commented:
It is set to Simple

??
0
 
mebaby333It AdminCommented:
did you shrink and truncate your log file and try it again?
0
 
mebaby333Connect With a Mentor It AdminCommented:
You could also try moving the log file as laid out in this article... very straight forward

http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/
0
 
JElsterAuthor Commented:
yes.. it then works then I will get TEMBDB LOG FULL after several queries
0
 
JElsterAuthor Commented:
I have over 300GB of free space on the drive... it makes no sense
Queries return max of 20K records
0
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
The log being full doesn't necessarily mean a lack of disk space. What are the growth settings on your tempdb log file?

Run the following query if you're not sure how to get the file growth info:
select * from tempdb.sys.database_files

Open in new window

The important numbers are size, max_size, growth and is_percent_growth for the row whose type_desc value is "LOG".
0
 
JElsterAuthor Commented:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tempdb.sys.database_files'.
0
 
Carl TawnSystems and Integration DeveloperCommented:
Guess that doesn't work in 2005 then, so you'll have to use longhand:
use tempdb
go
select * from sys.database_files

Open in new window

0
 
JElsterAuthor Commented:
same msg ????????????
0
 
Carl TawnSystems and Integration DeveloperCommented:
In that case just look at the properties in the GUI. Expand System Databases in the Server Explorer, right-click on tempdb, choose Properties and go to the Files page.
0
 
JElsterAuthor Commented:
36MG    tempdev
1 MG   templog
0
 
Carl TawnSystems and Integration DeveloperCommented:
I'm guessing that is the initial size column. What does the auto growth/max size column say?
0
 
JElsterAuthor Commented:
autogrowth 10 % unrestricted
0
 
Carl TawnSystems and Integration DeveloperCommented:
In that case it should be growing until the disk runs out of space. Is the disk filling up when you get the log space error message, or is there still free space on the disk?
0
 
JElsterAuthor Commented:
Plenty of free space 220GB
?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.