how to determine which process is ballooning tempdb

Help!
We have an exceptionally busy server with many databases used by many applications. One or more processes is causing tempdb to grow very rapidly and eat up the disk.
I need suggestions on how to figure out which of the hundreds of processes might be doing this. It's like trying to find a needle in a haystack. Is there some way to filter a Profiler trace which might help me identify who the culprit might be?
Thanks.Good question,
I don't think there is a direct way to do that in Profiler, but... if you
add some of hte AutoGrow events in database event class... and you're
tracking stmt:completed events... and looking at writes... you should be
able to see some type of correlation. Haven't messed around to look for the
particular model you need... but it's interesting. I'll take a look at see
if I find a nice way to do it...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Marie Ramos" <RamosMar@.saccounty.net> wrote in message
news:D4061B10-528B-42BE-A58F-BA6331F4D5F4@.microsoft.com...
> Help!
> We have an exceptionally busy server with many databases used by many
applications. One or more processes is causing tempdb to grow very rapidly
and eat up the disk.
> I need suggestions on how to figure out which of the hundreds of processes
might be doing this. It's like trying to find a needle in a haystack. Is
there some way to filter a Profiler trace which might help me identify who
the culprit might be?
> Thanks.
>|||Hi Marie.
I don't actually know of a conclusive set of profiler filters, perfmon
settings or sysprocesses columns that would give you what you're after per
se (hopefully someone does), but have you tried looking at the output of
sp_who2's DiskIO column?
This isn't going to give you precisely the answer you're after but if you
identify which processes are consuming large amounts of disk io & perhaps
relatively less cpu, you may be able to quickly eliminate many of the other
processes & concentrate on those with these crude characteristics.
Hopefully someone else can produce a more scientific method but this might
help you get started.
Microsoft runs a program called sqlwish for sql server which allows you to
submit requests for features. Your request for a feature to track which
process is causing tempdb to grow sounds a good one imo, as many would
benefit from such a feature - sqlwish@.microsoft.com.
Regards,
Greg Linwood
"Marie Ramos" <RamosMar@.saccounty.net> wrote in message
news:D4061B10-528B-42BE-A58F-BA6331F4D5F4@.microsoft.com...
> Help!
> We have an exceptionally busy server with many databases used by many
applications. One or more processes is causing tempdb to grow very rapidly
and eat up the disk.
> I need suggestions on how to figure out which of the hundreds of processes
might be doing this. It's like trying to find a needle in a haystack. Is
there some way to filter a Profiler trace which might help me identify who
the culprit might be?
> Thanks.
>sql