• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

TEMPDB LOG FULL

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

any ideas?
0
JElster
Asked:
JElster
  • 10
  • 7
  • 5
  • +1
3 Solutions
 
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
 
mebaby333Commented:
Is it set to simple recovery mode? I believe this will automatically truncate...

ALTER DATABASE tempdb SET RECOVERY SIMPLE
0
 
mebaby333Commented:
Is it running out of disk space?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
JElsterAuthor Commented:
Option 'RECOVERY' cannot be set in database 'TEMPDB'.

plenty of space...
0
 
mebaby333Commented:
are you  logged in with raised credentials? Do you have permission to change settings?
0
 
JElsterAuthor Commented:
yes
0
 
mebaby333Commented:
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
 
mebaby333Commented:
did you shrink and truncate your log file and try it again?
0
 
mebaby333Commented:
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 TawnSystems 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
 
mebaby333Commented:
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 10
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now