JDBC & Tempdb file growth

From: Josh _at_ American Data (_at_)
Date: 09/02/04 

Date: Thu, 2 Sep 2004 12:11:01 -0700

Hello,

Yesterday our tempdb exceded the remaining space on its drive, and our SQL
server instance became incapacitated. The file reached over 1.5gb.

I understand that this file grows as needed, and when the SQL server
instance is restarted it returns to an initial size of about 8mb.

I'm concerned that there is something odd with our environment that is
causing such tremendous growth.

The application using the DB is very simple. It is more or less a web page
that performs a query in 1 table. Yes, there are some other more elaborate
features, but this is the query that is performed 90% of the time. This page
is probably hit a couple of times a day... and the DB is very small (a couple
of megs).

I don't understand why the tempdb could be growing so large over time.
Yesterday we restarted SQL server, therefore reducing the file size of the
tempdb. Then, we hit the page about 30 times that performs this simple query
(a select and order on a table that contains about 3 rows). All of the
sudden, the file grew by about 4 megs. This seems odd to me.)

I've been monitoring the tempdb for a day now and it seems to keep growing,
and not shrinking or leveling. It is now at 90 megs.

Does the tempdb store information indefinately, or is it released when no
longer needed. I'm trying to pinpoint any potential problems. Here are some
of my hypothesis:

1.) We are using connection pooling. Is it possible that the pool keeps a
dedicated number of sessions with the DB available at all times, and
therefore, MS SQL server is never releasing temp info from the tempdb because
as far as it knows, the connections are never closed?

2.) We simply need to improve our SQL code, and remove complex querries and
order statements. Although, the one page I've referred to doesn't contain
any complex querries, it does contain an order by statement. This seems
highly unlikely this is the problem being that this table contains about 3
rows and the querry is fairly simple. Could this cause the tempdb to expand
so much?

3.) We have set a property in our jdbc connect: SelectMethod=Cursor. Could
this be a poor decision in the way it effects the tempdb growt? Isn't it
needed if we are using result sets?

Any thoughts are greatly appreciated. Please let me know if you would like
any more detailed information and I'd be glad to assist.

Thanks to anyone who is willing to assist in advance.

Relevant Pages

  • Re: Tempdb after restart
    ... If SQL has to auto-grow tempdb there are severe ... performance penalties. ... Microsoft SQL Server MVP ... I support the Professional Association for SQL Server ...
    (microsoft.public.sqlserver.server)
  • Re: sql service starts then stops after master restore
    ... Maybe the folders where SQL Server tries to create the tempdb files do not ... Delete old file after restarting SQL ... > after I restore master db on another server (msg pops up and says restore ...
    (microsoft.public.sqlserver.server)
  • Re: Tempdb Log
    ... databases (including tempdb) using the simple recovery model on our SQL ... Server 2000 sp3 on Windows 2003. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server 2000, Raid 1 or Raid 5 for Applications
    ... This depends entirely on the degree of use of tempdb. ... "By segmenting tempdb onto its own RAID ... Andrew J. Kelly SQL MVP ... drives RAID 10 with user files and TempDB data files ...
    (microsoft.public.sqlserver.setup)
  • Re: User deleted
    ... tempdb, because the tempdb database is completely recreated every time SQL ... Server is started. ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
Loading