Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TEMPDB LOG FULL

Posted on 2014-04-23
23
Medium Priority
?
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 5
  • +1
23 Comments
 
LVL 27

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1336 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
 
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 664 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 1336 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

609 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