Growth of Tempdb database

Growth of Tempdb database

Post by Brendan Coyl » Wed, 14 Aug 2002 20:23:04

I have a stored procedure (SQL Server 2000) to recreate a fact table by
joining a number of base tables together. The process creates a number of
tables (I avoid creating temporary tables). Once the fact table has been
built, other intermediate tables are deleted.

I am finding this procedure causes Tempdb to grow abnormally in size. I
rebuilt indexes on the main tables used in the procedure, but it has not
solved the problem. Am I missing something simple here?




Growth of Tempdb database

Post by Bob Pfeiff [MS » Wed, 14 Aug 2002 21:32:39

If you have GROUP BY and/or ORDER BY clauses in the SQL that you use to
create the fact table, worktables are created in tempdb to resolve them.  If
there is a lot of data, you can expect tempdb to expand.

The best bet is to set your tempdb to a size adequate for these operations
so you don't have the overhead of allocating new extents as your SQL
executes.  You might also consider putting tempdb on its own set of disks if
this is a long-running operation on huge amounts of data.

Microsoft Consulting Services
This posting is provided AS IS with no warranties, and confers no rights.


1. Java and tempdb excessive growth


We have developed an application in Java. We are using a JDBC (from
Microsoft - version 1.2.2 sp1) in order to access a MS SQL Server
(2000, in a W2k box).

In our development environment, everything works fine.

But, in our production environment, the tempdb has excessive growth.
When it reaches the disk size limit, I receive an error message
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Could not perform the requested operation because the
minimum query memory is not available. Decrease the configured value
for the 'min memory per query' server configuration option.".

We have already changed the min memory per query to its minimun value
(512) and the error persist.

In our development environment, the tempdb has 1mb. In our production,
the size is 2GB.

In our development environment, we have simulated 80 concurrent
connections, and the tempdb never got more than 0.75 mb. In our
production environment, with 10 connections, the tempdb growths more
than 1.2Gb.

We have double checked the configurations, and it seems to be the same
beteween the two databases...

I don't think that we area doing something wrong (like a bad query, or
some mistake opening/closing connections), although we have checked
the source code also...

We are using connection pool. In the production environment, the only
way to release some tempdb resouce, is killing some connections.

Does someone has some idea?


Bob Rivers

2. Oracle Mail ??

3. SQL TempDB growth/SQL 2000

4. Empress?

5. TempDB file growth SQL 7.0

6. how can i compare the databases?

7. TempDB.mdf growth in SQL Server 2000

8. Having problems runninig Informix 4.0 under a dosbox on win95. Any suggestions.

9. Scan cycling, tempdb growth

10. Log and TempDB: Unrestricted Growth?

11. Log file and TempDB : unrestricted growth?

12. TEMPDB growth related to swap/page file

13. TEMPDB Growth