TempDB won't shrink

[Thomas R. Hummel]

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.

[Erland Sommarskog]

Thomas R. Hummel (to********@hotmail.com) writes:

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.

Autoshrink on tempdb? That does not sound like a good idea.

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 '#%'

In SQL 2005, tempdb is used for more things in the past. One thing that
comes into mind is the version store which is used for snapshot isolation,
triggers, MARS and reindexing.

Reading in Kalen Delaney's "Inside SQL Server 2005: The Storage Engine",
you should first look at sys.dm_db_file_space_usage and see what numbers
you have there. You can then proceed to sys.dm_db_session_space_usage
and sys.dm_db_task_space_usage .

I recall that Kalen had one or two columns in SQL Server Magazine on
monitoring the version store. If you have access to their archive,
it may be worth trying to find those columns.

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

[Thomas R. Hummel]

Thanks for the suggestions Erland.

Auto shrink was just a test by one of their tech guys and it's off
now. After reading through Kalen's articles on the subject and doing
some checking I think that the situation was caused by triggers on
some of the tables causing the use of the versioning store. I'll be
doing some testing on it tonight and then working to determine an
appropriate size for TempDB.

I have a couple articles on determining disk space for TempDB, but if
you have any suggestions it would be appreciated.

Thanks!
-Tom.