Posted by Erland Sommarskog on 05/10/06 00:15

SQL Server (anonymous@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: T-SQL Debugger Doesn't Allow Stepping Through Stored Procedures no other procedure with the same name
• Prev in forum: Tempdb grows rapidly and fills up disk space
• Thread view: Re: Tempdb grows rapidly and fills up disk space

[Reply to this message]