I am using a SQL6.5 server for my application. My application involves thousands of operations (insert, modify and deletes) on one of the databases i.e. database called 'mydb'; with triggers installed for the operations performed on the tables of 'mydb' database. The 'tempdb' of my server is consistently growing due to these operations. The data space available on the 'tempdb' database is depleting extremely fast. I would like to know what is the way to control this? I suppose my database server will run into trouble if my tempdb database runs out of space.
If temdb runs out of space the server will probably crash.
tempdb should be set to truncate the log on checkpoint.
Check which tables are in existence in tempdb - it sounds like either you have very long running queries which are holding large tables in tempdb or long running transactions - both indications of poor code / design.
try dbcc opentran (dbname) on all databases - transactions should only last seconds - if you have long running ones find out why.
Do select * from tempdb..sysobjects and check what the owners of the tables are doing - you'll probably find that tables are being created but not dropped or that unneeded sorts are being performed.
Sometimes it's not just transactions "BEGIN TRAN...COMMIT TRAN" that you need to look at. Any queries that require working space will eat up tempdb. Look for queries that use DISTINCT, GROUP BY, ORDER BY, subSELECTS. This queries require SQL Server to create temporay working tables that will be placed in tempdb.