SQL 2000: what to do if tempdb grows to be very big
Unless your workload changed significantly, shrink file is not going to help you because tempdb will just grow back if a query needs that amount of free space. So a better way is to identify the query and try to optimize that, maybe by adding an index. The query that needs a lot of space in tempdb usually is a long running query. So you could start from the longest running queries. If you see a spool, or sort, or hash join in the query plan, that could be an indication of tempdb usage.
To prevent tempdb from consuming too much space on your disk, you could set up a max size for tempdb file. However, if you do this, make sure it is large enough. Otherwise your query may fail if it could not get the needed space.
On SQL 2005 there are better ways. I will post something later.
