Since the past couple of months we have been seeing the tempdb grow from 5GB to 22GB about once every week. The production database is about 35GB. Since there is quite a bit of activity on the server i am finding it difficult to try to isolate what caused this sudden increase in the tempdev size. The actual data in tempdev less then 2 MB but it holds on to the 22GB till i go in and shrink it.
This has led to us having serious space issues on the server.
Any advise on how i would be able to trap the query that might cause this behavior would be great.
As an immediate measure to prevent space issues on your server, I would set a maximum limit on the growth for the tempdb data file. I have heard elsewhere (but seen no documentation) that 1.5X RAM is a good starting point (and yes, I realize that this is the same as for the paging file, but I do recall that this is a separate setting).