One of our customers has a problem with a large number of open tables while working with our application. The number of open tables goes up during the day when users work in the application, and goes down during the evening when users log out. It looks like a wave pattern.

The open tables (as shown by 'open tables' in Arc32) consist of the following :

About 150-200 'real' tables. (tables that are created by us and used in the application). The rest are .tmp files. The database-server settings are configured for 6000 open tables and when this is reached, the ADS Server crashes.

The .tmp files are in the following formats (* : five hexadecimal values) :

  • 50% tim*.tmp
  • 50% ti*.tmp

My questions:

  • What's in the .tmp files ? (my guess would be : temp-tables, but maybe they have multiple purposes)
  • Why are so many .tmp files created and only cleaned up when I close the application ? (especially the last part of the sentence is important)
  • What can I do to prevent this ?

The server has no TMP_FILE_POOL_SIZE in the registry, so I presume that the number of temp files was limited to a default of 200.

We have a similar problem with the number of work areas. They are only cleaned up when the application is closed.

We use Advanatage Database Server version and Delphi TADSTable and TADSQuery components. They are created when needed and destroyed when they are no longer used (try..finally). This basically rules out the possibility of open TADSTable / TADSQuery objects keeping the .tmp files in place.

asked 09 Feb '15, 08:03

APriem's gravatar image

accept rate: 0%

edited 09 Feb '15, 08:05

these are intermediate result sets and temp index files. Looks like you're creating a lot of queries which are not cleaned up (Query objects free'd but not closed?).


answered 09 Feb '15, 08:15

Joachim%20Duerr's gravatar image

Joachim Duerr
accept rate: 18%

Are you saying that .tmp files are restricted to Query objects or could they also be created by table objects ?

I checked the places in our software where queries are fired most often, and they always seem to be opened, then closed, then freed.

When should these .tmp files be removed from disk ? When the Query object is closed ? Destroyed ?

(09 Feb '15, 08:32) APriem

Yes, those .tmp files are most certainly from query objects leaked by the application. They will be cleaned up when the connections that the queries opened on are closed. You can find out what queries are leaked with the sp_GetSqlStatements. Looking at the "Start Time" column for the oldest queries and it should provide idea on the code that is not freeing the query properly.

(09 Feb '15, 09:34) Alex Wong

Nice. This helped a lot. I was under the impression that the .tmp files had something to do with temp-tables. But they're linked to queries. Using the sp_GetSqlStatements procedure we found the culprits in our code. Thanks a bunch !

(10 Feb '15, 04:57) APriem
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 09 Feb '15, 08:03

Seen: 2,723 times

Last updated: 10 Feb '15, 04:57

Advantage Developer Zone Contact Us Privacy Policy Copyright Info

Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.