Hello,

I am working on something and could use some input. As this is a customer
site, I need a specific plan, and can't play around too much, so I am looking
for guidance. I would be very grateful for any feedback anyone can give. Feel
free to throw out any ideas you might have.

I am trying to troubleshoot a situation where TempDB grows to the limits of
its disk, requiring a SQL restart to become usable again. TempDB will be
2-4GB for an extended period of time under a decent load, then grow by 20GB
in 45 minutes, hitting 30GB (disk limit) in another 30 minutes.

I believe (through querying sysprocesses, checking for open transactions,
Googling, etc) that the growth is a result of processing large result set(s).
Is there any way, besides Profiling for expensive queries, that I can find
this out for sure? If the query does not complete before TempDB gets too big,
then Profiler doesn't report a Complete Event with the query costs, right?

I am thinking to next try capturing the delta in sysprocesses for cpu and
physical_io. What other approaches might work?

Aside: another idea I am working on is to try and pick up a high estimated
cost from Show Plan All. It seems I will need to parse and query the
BinaryData field to be able to mine any Profiler log data for high
TotalSubtreeCost. Is there another way to go about this?

Again, any feedback or suggestions anyone could give would be most welcome.

Thank You,
Eric Proegler