Solved

TEMPDB LOG FULL

Posted on 2014-04-23
23
323 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 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