Error 1105

I'm looking for some advice please.
I have a production database for financial services. The front-end
application is from CODA. Financial. This is the only app that access this
database. Every now and again, some process(es) will need more tempdb space
then the current size. So SQL will auto grow that database. Our problem is
that the drive will eventfully be consumed, and then ... well you can guess.
So, we have done 3 things.
1: create a job that will, weekly, shrink the tempdb logfile during off
hours on the weekend.
2: We set a max size for the tempdb. This was done in an attempt to isolate
the process(es) that where eating up tempdb. However, this seems to be no
big deal for the user(s) when they get the 1105 error. No user has
complained and no process, or job, has crash because of it.
3: I create an alert and job to be run when an Error 1105 is recorded. The
job will exec SP_WHO2.
Now one of 2 things will happen when an Error 1105 is triggered.
1: The sp_who2 will, sometimes, capture the user.(almost always the
application login). However, what I don't get is anything that will lead me
to the actual user: such as - network address, host name of user, etc.. So,
no way to back track to the user/process and what they are doing.
2: By the time the alter triggers the job, the offending user is
disconnected from the SQL Server and all I'm left with is the spid in the
sql error log.
I'm open to any advise, suggestions or thoughts on a better way to track
down the process(es) that keep consuming tempdb.
TIA
Joe
Audit Logging. You could use a profiler as well but be careful on the no.
of events you use as it could eat up disk space.
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.