By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,636 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Tempdb grows rapidly and fills up disk space

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
SQL Server (an*******@devdex.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..sysobjects (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..sysindexes (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****@sommarskog.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 discussion thread is closed

Replies have been disabled for this discussion.