Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/default.aspx?scid=kb;en-us;834846&Product=sql2k
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>|||Some information here as well
http://www.aspfaq.com/2446
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>