TempDB won't shrink

I was able to find a few posts on this topic, but none of them quite
seemed to fit the situation, so I'm hoping that someone else might be
able to help me here.

I have a client who is using SQL 2005 (sorry, don't have the exact
build with me). They run a weekly process which causes TempDB to grow
to over 100GB before it fails due to a full disk. Once it's grown to
that size we can't seem to shrink it again short of restarting the
server.

The database is set to Simple recovery mode and I believe that it is
set to auto shrink.

Here are some things that found out/tried:

DBCC SHRINKFILE (tempdev, 50000) does nothing.

DBCC OPENTRAN returns no transactions.

If I look in TempDB for any temporary tables, I get a couple dozen.
They all have zero rows in them though. I didn't think to look at the
columns that they contain, but maybe that will give me an indication
of their use. I used SELECT OBJECT_NAME(id), rowcnt FROM
tempdb..sysindexes WHERE OBJECT_NAME(id) LIKE '#%'

sp_spaceused shows that almost none of the space is actually being
used.

I've looked for reindexing operations in their code and didn't see
any, but there's quite a bit of code there. While there are some
operations against some very large tables, I didn't see any obvious
cartesian products or sorts either. Again though, there's a lot of
code and I haven't profiled much of it.

My plan right now is to reboot, set up a trace to track both file
growths and SQL statements and then see if I can find which
statement(s) are causing the TempDB to grow to be so large. Any
suggestions on additional things to track? Even given this though, I
don't know if that will help me with the shrinking issue, except to
possibly prevent it from being necessary in the future.

Any advice or suggestions welcome. Please let me know if I've left out
any important information. I always seem to forget at least one
obviously important bit of information. :-)

Thanks!
-Tom.
.

Relevant Pages

  • Re: database space
    ... Because page locking in work table is unreliable, shrink ... skips work table and work file during tempdb shrink. ... file to a smaller size - the result will translate to a lot of 'unallocate ... Any object/index could use the space. ...
    (microsoft.public.sqlserver.server)
  • Re: database space
    ... I would like to know when my tempdb starts to grow and caused by ... it means the database used very little space in ... >> skips work table and work file during tempdb shrink. ... >>>> Microsoft SQL Server Storage Engine ...
    (microsoft.public.sqlserver.server)
  • Re: A very strange behaviour
    ... > The result of the reports started to produce different results every time ... I execute the procedures, after hours of investigation I tries to shrink the ... When I shrink the tempdb again the same behavior repeated again. ...
    (microsoft.public.sqlserver.programming)
  • Re: Does shrinking a user database cause tempdb to grow?
    ... idea stuck with me. ... It didn't make any sense for temdb to be affected ... during a shrink operation but I had to throw the question out there. ... >> inexplicably large jump in size for my tempdb. ...
    (microsoft.public.sqlserver.server)
  • Re: database space
    ... I read it somewhere that tempdb size is about 25% of user db, ... Also I read that often shink database will fragment db and file system. ... Because page locking in work table is unreliable, shrink ...
    (microsoft.public.sqlserver.server)
Loading