473,883 Members | 1,622 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

log file for database 'tempdb' is full

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

1. My log file drive has more than 20 GB free out of 30 GB
2. Both data file & log file has default setting on unrestricted file
growth by 10%
3. Currently we moved from SQL 7.0 to SQL 2000 & the load in the user
side also doubled
4. We can't do the temporary solution like restarting the server or
SQL service, because the application is a real time system with much
less manual interaction.

Thanks in advance.

Regards
Seni
Jul 20 '05 #1
1 15614
Senthuran (se******@yahoo .com) writes:
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......

1. My log file drive has more than 20 GB free out of 30 GB


It appears that you have some operations that take a serious load in
tempdb. Could be worktables, could be temptables that grow a lot,
and which see a lot of updates. I am afraid that you need to track
down which operations this might be.

When you say that there is 20 GB free, is this just when you have
gotten this message, of after you have restarted SQL Server?
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

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

Similar topics

2
3743
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
3905
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
1656
by: Query Builder | last post by:
I am new to BCP. Can anyone help me understand this? I tried searching the BOL but it doesnt show much help on syntex. Lets say I have a simple table and want to out put the records into a file. Then with that output file, I want to create a format file using the BCP utillity and builk insert it into another table. Can someone explain me the syntex involved in this.. No need to go so elabrate.. Just give me a basic senario and just the...
6
2343
by: Mortrek | last post by:
Hi. We have a .NET programmer using our SQL server. The database is roughly 8GB at this point, full of all of our inventory, payroll data, etc. for running reports. This is on a dual-xeon 3.2Ghz, 4GB DDR, 3x15k RPM SCSI RAID-5, gigabit ethernet, etc server. Yet, every once in a while it slows to an absolute crawl. IIS6 is the front-end, and its limited to a web garden with 4 threads. However, sometimes only 1 processor does any real...
3
6687
by: New MSSQL DBA | last post by:
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.
9
12669
by: gopi | last post by:
when installing db2, it only asks for installation directory and does not ask where it is going to store data files. how can i change the default directory after installation.
21
28064
by: Peter Nurse | last post by:
I have just upgraded to SQL Server 2005 from SQL Server 2000. In Microsoft SQL Server Management Studio, when I click on database properties, I receive the following error:- Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. (Microsoft SQL Server, Error: 468)
2
1581
by: David C. Barber | last post by:
This updates a previous post. We have updated our SQL Server 2000 to new hardware by reinstalling SQL Server 2K to a new, dual processor server. We performed a full SQL backup of the entire server, and did a full restore onto the new hardware, which has the same server name and IP address. One difference has bitten us, however. When I perform an sp_who standard procedure call in VB6, the dbname field comes back with our database...
1
1685
by: ShivBahadur | last post by:
Hi All, how to remove "tempdb is full error in sqlserver2000"
0
9942
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
9792
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10857
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10419
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
9577
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
7974
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
7133
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
5801
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...
0
5994
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

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.