Posted by Thomas R. Hummel on 01/18/08 20:47

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.

[Back to original message]