|
|
Posted by Erland Sommarskog on 01/18/08 22:31
Thomas R. Hummel (tom_hummel@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, 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
Navigation:
[Reply to this message]
|