Solved

TEMPDB LOG FULL

Posted on 2014-04-23
23
320 Views
Last Modified: 2014-04-25
Constantly getting this error on large queries.
Have tried to truncate every 4 hours.
Log is set autogrowth

any ideas?
0
Comment
Question by:JElster
  • 10
  • 7
  • 5
  • +1
23 Comments
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40018406
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
 
LVL 5

Expert Comment

by:mebaby333
ID: 40018577
Is it set to simple recovery mode? I believe this will automatically truncate...

ALTER DATABASE tempdb SET RECOVERY SIMPLE
0
 
LVL 5

Expert Comment

by:mebaby333
ID: 40018581
Is it running out of disk space?
0
 
LVL 1

Author Comment

by:JElster
ID: 40018671
Option 'RECOVERY' cannot be set in database 'TEMPDB'.

plenty of space...
0
 
LVL 5

Expert Comment

by:mebaby333
ID: 40018700
are you  logged in with raised credentials? Do you have permission to change settings?
0
 
LVL 1

Author Comment

by:JElster
ID: 40018719
yes
0
 
LVL 5

Expert Comment

by:mebaby333
ID: 40018779
In SQL Enterprise Manager, right click on the database then choose properties and then Option  and Recovery change from "Full" to "Simple"
0
 
LVL 1

Author Comment

by:JElster
ID: 40018795
It is set to Simple

??
0
 
LVL 5

Expert Comment

by:mebaby333
ID: 40018816
did you shrink and truncate your log file and try it again?
0
 
LVL 5

Assisted Solution

by:mebaby333
mebaby333 earned 334 total points
ID: 40018839
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
 
LVL 1

Author Comment

by:JElster
ID: 40018845
yes.. it then works then I will get TEMBDB LOG FULL after several queries
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:JElster
ID: 40018851
I have over 300GB of free space on the drive... it makes no sense
Queries return max of 20K records
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 166 total points
ID: 40019671
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
 
LVL 1

Author Comment

by:JElster
ID: 40019679
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tempdb.sys.database_files'.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40019685
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
 
LVL 1

Author Comment

by:JElster
ID: 40019698
same msg ????????????
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40019701
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
 
LVL 1

Author Comment

by:JElster
ID: 40019727
36MG    tempdev
1 MG   templog
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40019730
I'm guessing that is the initial size column. What does the auto growth/max size column say?
0
 
LVL 1

Author Comment

by:JElster
ID: 40019755
autogrowth 10 % unrestricted
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40019769
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
 
LVL 1

Author Comment

by:JElster
ID: 40019786
Plenty of free space 220GB
?
0
 
LVL 5

Accepted Solution

by:
mebaby333 earned 334 total points
ID: 40020181
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now