473,698 Members | 2,615 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Tempdb grows rapidly and fills up disk space

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 ***
May 10 '06 #1
1 7224
SQL Server (an*******@devd ex.com) writes:
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.


There are a couple of possible reasons for tempdb filling up. The
most obvious is of course a process that fills up a large temp table.
One way to investigate this, would be to do:

SELECT * FROM tempdb..sysobje cts (NOLOCK) WHERE xtype = 'U'

This tells you, if there are any temp tables at all right now. You might
be able to get an idication of sizes by lookin in sysindexes:

SELECT object_name(id) , reserved
FROM tempdb..sysinde xes (NOLOCK) WHERE indid in (0, 1)

reserved gives you the size in 8192 KB pages. But the numbers may
lag behind and not be accurate.

There are other sources than temp tables: sort operations, cursors,
worktables for queries. These are more difficult to track down.

You could shrink tempdb, and trace for auto-grow events, and see if you
can connect the events to a certain user or host.

By the way, to which size did tempdb grow? How large is your largest
database?

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 10 '06 #2

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

Similar topics

3
6296
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
4482
by: Paul | last post by:
Hi: I am totally new in Sun Solaris. I am not sure someone would like to tell me how to rellocate Sun Solaris disk space where oracle installed? I installed Oracle 8i in Sun Solaris and set Oracle main table space called TABLES in unix disk space /dpp/live01. From the following, it's quite easy to see that the disk space of /dpp/live01 is 97%.Therefore how can i move some disk space of SWAP to /dpp/live01 without losing
2
3223
by: Sagar Choudhary | last post by:
Is there any exception related to disk space ? If not how else can we check the availability of the disk space in a c++ program. I know ostream helps a bit. When we try to open a file when there's no disk space ostream::rdstate() returns badbit.
3
6674
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.
6
2075
by: Anthony Robinson | last post by:
I am trying to instal FixPak 4 for V8 on XP. I kept getting an error. During the install, I kept getting the "low disk space on c:" message. My C; drive has 1.4 GB of free space. I can't believe that installing a fixpak (temp files and all) would take up over aGB of space - but it is!!! How can I get this installed? All the programs that are on my machine need to be there, so there's not the option of deleting stuff or removing...
12
11005
by: New World Order Pigs | last post by:
Is there no way in .net to get disk space remaining for a given drive??? I can't believe it and yet is seems to be so. If someone knows of a way to do this in the .net libraries I'd be very much appreciative. Thanks, LT.
0
2487
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...
3
1898
by: A P | last post by:
Hi, I am loosing disk space and wanting to install .Net Framework on my W2KSP4 Server. I only have 400MB available, is this enough for me to install .Net Framework? Me
0
3931
by: DannielHS | last post by:
- Windows created the low disk space warning to alert computer users when their hard drive is running low on available disk space. Then you will see on a fairly regular basis as your hard drive fills up is the Low Disk Space warning in SBS Server: "You are running out of disk space on Local Disk . To free space on this drive by deleting old or unnecessary files, click here." - Sometimes, you may just need to ignore or disable your Windows low...
0
8612
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,...
0
9171
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8880
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
7743
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
6532
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
5869
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
4625
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3053
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
3
2008
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.