?
Solved

TEMPDB LOG FULL

Posted on 2014-04-23
23
Medium Priority
?
329 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

765 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