Error 1105

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: JoeyDBA (JoeyDBA_at_yahoo.com)
Date: 07/16/04 

Date: Fri, 16 Jul 2004 14:58:03 -0500

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

Relevant Pages

  • Re: tempdb size question
    ... TempDB is the soul of SQL and everything you can do to gain performance, ... For example, SQL OLTP with 4 processors, I create five files, 4 with the same ... database size. ... TEMP be in total? ...
    (comp.databases.ms-sqlserver)
  • Re: tempdb can not delete it
    ... Andrew J. Kelly SQL MVP ... > Yes, i konow it is regenerated normally on restart, but it does not ... Why would you want to delete that database? ... >>>is now 2.8 GB and I can not delete tempdb. ...
    (microsoft.public.sqlserver.setup)
  • Re: tempdb can not delete it
    ... Andrew J. Kelly SQL MVP ... > Yes, i konow it is regenerated normally on restart, but it does not ... Why would you want to delete that database? ... >>>is now 2.8 GB and I can not delete tempdb. ...
    (microsoft.public.sqlserver.setup)
  • I am getting tempdb full error again
    ... The log file for database 'tempdb' is full. ... Back up the transaction log for the database to free up some log space.. ... exec sp_helpdb tempdb ...
    (microsoft.public.sqlserver.server)
  • Re: Thank you very much...again
    ... For information about the Microsoft Strategic Technology ... Protection Program and to order your FREE Security Tool Kit, ... | delete..etc permissions on tempdb. ... |>database which would avoid some of this. ...
    (microsoft.public.sqlserver.security)