Tempdb Log File

Hello all I am new here so I am sorry if this question has been asked 1,000,000 times.

First let me describe my environment:

VM-Ware ESX 3.01
3.0Ghz (Dedicated to VM)
3.0 GB RAM (Dedicated to VM)
Windows Server 2003 SP2
SQL Server 2005 SP2
Spotlight for SQL Server
Quest SQL Watch

Now the issue:

We are in current development of a new manufacturing system so the above system is just a development box at this stage. The problem that I am having is that the tempdb log file seems to grow out of control, and if I do not have time to shrink it, I have to bounce the mssql service in order to do anything once the disk is completly full. At one point I even made a job to shrink the log file every 15 minutes to try and prevent the uncontrolled growth.

I have researched this everywhere and it really is starting to get frustrating. I am highly leaning towards the application that is utilizing the database is heavily dependant on #temp tables. So I found this query to return me the top colums being used in the tempdb.


SELECT OBJECT_NAME(id), rowcnt

FROM tempdb..sysindexes

WHERE OBJECT_NAME(id) LIKE '#%'

ORDER BY rowcnt DESC


And I get the table that is using up the most rows.

SELECT *

FROM tempdb..sysindexes

WHERE OBJECT_NAME(id) LIKE '#4440A7FE'

After doing this I figured out that there is something that is writing to the tempdb around every 15 seconds about 100000+ rows at a time. And the alarming thing about it is that the record "rowmodctr" is already over 1 billion. So that leads me to beleive that this has modified rows over 1 billion times since I last rebooted the server a week ago.

So the main question that I have is. Is there a way to query what is inside that '#4440A7FE' table, so that I can pin it down to a session?

Also I have already said to the development team to use "derived tables" instead of temp tables, but they just fight with me all the time and blame the problem on hardware and configuration.

Please please help me with this as it getting really frustrating I am so sick of shriking that file. And sometimes it grows more that 3GB in less than 15 minutes.