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

SQL Server (anonymous (AT) devdex (DOT) com) writes:
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, esquel (AT) sommarskog (DOT) 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