can I monitor what goes in tempdb

Hi,
I've noticed that our application occassionally causes the tempdb log file to grow quite large. I'm not a dba, nor do we have one onsite, so I'm trying to read up on things that cause the tempdb file to grow. Having done that, I don't see anything obvious that our application is doing (checked things like temp tables, cursors, group by, order by, etc).

So I was wondering if there is any monitoring that I could do on the tempdb itself to see when and how it is being accessed? And what kinds of things are getting stored in tempdb? If my application does create objects in the tempdb table and then deallocates the object, shouldn't that keep the size of the tempdb in check? Or would that not have any effect on the tempdb.log file?

Also, it is the tempdb.log file that grows quite large - is there a way programmatically to clear the log file? I know there is a shrinkdb option from the Enterprise Mgr. Should my application periodically be doing something to shrinkdb (assuming it does use tempdb appropriately) in order to keep the size from growing too much?

Thanks in advance,
Beth

there are many system maintenance commands (DBCC) consumes the tempdb. also check for any open transaction (DBCC Opentran). To monitor usage of Tempdb , either u can using tracing sp or Profiler with proper filter like database name etc. Also, you can consider a job which shrink the tempdb log file when less traffic is there.

refer : http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

the link is related to SQL Server 2005 but more or less it is applicable to other versions also

Madhu

|||I forgot to mention that we are still on SQL Server 2000. Thanks for the link - looks like it has some good info in it, so I'm glad most of it applies to other versions.

Beth