|
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) :
My questions:
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 11.10.0.20 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. |
|
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?). 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
|
