473,706 Members | 3,816 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

tempdb log full error with plenty of disk space available

has anyone met with this before?

the setting is SQL2K with SP3 on a 2 node active-active W2K3 cluster.

on one of the machine, it occasionally prompts for the following error:
"The log file for database "tempdb" is full. Back up the transaction
log for the database to free up some log space."

the problem is, at the time of error, the tempdb tx log is only 200MB
and there are over 50G disk space available.

settings of tempdb:
-- 10% autogrow, unlimited max size
-- auto shrink off
-- data file around 1G

Thanks.

Jul 23 '05 #1
3 6675
Stu
Haven't ever seen that error, but stepping back a bit, it sounds like
you need to resize your tempdb. As you probably know, file I/O is very
expensive for processing power. If your tempdb is growing, then you
should resize it anyway.

That being said, that still doesn't solve your problem; it just delays
it a bit. But by changing the size, maybe some other erro will come to
light.

HTH,
Stu

Jul 23 '05 #2
I've found nothing in Microsoft's KB but found that some ppl seems to
have similar problem on other sql server forum.

Their explanation is that the TEMPDB is simple not large enough and
fast enough to handle a sudden query request of large temp space for
say sorting.

Their solution seems to increase the TEMPDB size to a larger size.

Further that, these errors occur after the server upgrade (from W2K to
W2K3, CPU 4x --> 8x, RAM 4G --> 12G).

Anybody has more input? Thanks

Jul 23 '05 #3
Hi

Adding the RAM probably increased performance, by adding more concurrent
query support, thereby pushing your existing TempDB size past it's limits.

Grow your TempDB to a size that is big enough.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.n et

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"New MSSQL DBA" <bo*******@gmai l.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .
I've found nothing in Microsoft's KB but found that some ppl seems to
have similar problem on other sql server forum.

Their explanation is that the TEMPDB is simple not large enough and
fast enough to handle a sudden query request of large temp space for
say sorting.

Their solution seems to increase the TEMPDB size to a larger size.

Further that, these errors occur after the server upgrade (from W2K to
W2K3, CPU 4x --> 8x, RAM 4G --> 12G).

Anybody has more input? Thanks

Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
15608
by: Senthuran | last post by:
Hi I am getting this common error once or twice a day: Error: 9002, Severity: 17, State: 2 The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space. provided......
10
13271
by: Jay Chan | last post by:
I keep getting the following error message when I run a serie of SQL commands: Server: Msg 9002, Level 17, State 6, Line 15 The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space. I have tried "dump transaction tempdb with no_log" right before I run the SQL command. But that doesn't help.
2
3726
by: Warren Wright | last post by:
Hi All, First, where can I get some questions of this sort answered? Preferably, are there good books or online guides that I can consult for these types of answers when necessary? 1. How do I know, when executing a query from Query Analyzer or otherwise, how many temporary tables will be needed, and how big they will be? 2. Where will those temporary tables be created? Always in tempdb?
2
3895
by: Tom | last post by:
I received an error that the log in tempdb was full, but the log and data segments are set to automatically grow with no limit AND there is plenty of available space on the disk. So I don't understand why this error occurred. Does anyone have any ideas? Thanks, Tom
1
6655
by: Dwayne | last post by:
I'm getting this strange error when I try to compile a project with large (100 - 200 MB) resource files. The error is as follows (I've removed the filename for simplicity): fatal error CS0009: Metadata file '' could not be opened -- 'Not enough storage is available to process this command.' I have plenty of disk space on my build drive, (10 GB
0
2489
by: Jas Shultz | last post by:
I'm using Win2K3 Enterprise edition with the latest .NET framework installed. I have this problem with getting "out of disk space" errors. It doesn't happen all the time but it does happen. When I installed Sharepoint Services I got the error and had to reboot and then everything was fine. I get it sometime when running aspx pages. It's very odd. Usually, I can just remove the application and readd it from the properties menu in the IIS...
1
7227
by: SQL Server | last post by:
Hi, The tempdb file on one of our servers grew very large and used all available disk space. This is SQL Server 2000 SP4. I have installed hotfix version 8.00.2187. I opened a profiler trace but can't still get to the root of the problem. Any help will be appreciated. Egbon *** Sent via Developersdex http://www.developersdex.com ***
2
3235
by: francois1 | last post by:
I am running a website with a SQL Server database attached. My transaction logs are full and my hosting co. won't allocate more disk space for me. I need to delete my database transaction logs and asume I will need to run an SQL script to do this. Problem: I do not have MS Enterprise Manager of any database utility on my website apart from MS Access. Where can I download a free SQL tool that I can use to delete the transaction logs...
2
12716
by: Thomas R. Hummel | last post by:
I was able to find a few posts on this topic, but none of them quite seemed to fit the situation, so I'm hoping that someone else might be able to help me here. I have a client who is using SQL 2005 (sorry, don't have the exact build with me). They run a weekly process which causes TempDB to grow to over 100GB before it fails due to a full disk. Once it's grown to that size we can't seem to shrink it again short of restarting the server.
0
8780
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8989
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7901
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6614
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5938
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4445
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3145
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2499
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2092
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.