Re: JDBC & Tempdb file growth

• Previous message: SqlJunkies User: "Re: Issue connecting through firewall using jdbc connector."
• In reply to: Josh _at_ American Data: "JDBC & Tempdb file growth"
• Next in thread: Brian Derwart: "Re: JDBC & Tempdb file growth"
• Reply: Brian Derwart: "Re: JDBC & Tempdb file growth"
• Messages sorted by: [ date ] [ thread ]

Date: Thu, 9 Sep 2004 20:23:01 +0200

Hi,

A way to avoid this is to use the "simple" recovery model setting of the
database. You can find this setting at Database ==> properties ==> tab
options ==> at "Recovery setting". The default setting is "full" which uses
a huge amount of tempdb. Most of the time it is not really needed when you
do not need to recover certain transactions after a crash.

Regards,

Frank.

"Josh @ American Data" <Josh @ American Data@discussions.microsoft.com>
wrote in message news:66BFB02F-6E53-4CD3-8612-03BA79F88DBD@microsoft.com...
> 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.

• Previous message: SqlJunkies User: "Re: Issue connecting through firewall using jdbc connector."
• In reply to: Josh _at_ American Data: "JDBC & Tempdb file growth"
• Next in thread: Brian Derwart: "Re: JDBC & Tempdb file growth"
• Reply: Brian Derwart: "Re: JDBC & Tempdb file growth"
• Messages sorted by: [ date ] [ thread ]