Kbase P33960: Certain SQL-92 queries are giving error 7631
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/14/2008 |
|
Status: Verified
FACT(s) (Environment):
Progress 9.x
All Supported Operating Systems
SYMPTOM(s):
Certain SQL-92 queries are giving error 7631.
Unable to allocate disk block for temp table use [MM - No data block] (7631)
CAUSE:
The SQL-92 server process while executing a query or command needs to use SQL temp tables. Temp tables are used for aggregation, sorting, update statistics usage, etc.
The SQL-92 server needs to allocate a disk block for some temp table, but it failed in the allocation.
Among the possible reasons, there are:
- disk space not available in chosen work directory,
- SQL-92 limit for temp tables exceeded,
- I/O failure on disk device,
- incorrect use of indexes.
FIX:
- Use indexes where possible and verify this with _Sql_Qplan (See Solution 20007). You can eventually run UPDATE STATISTICS to improve performance (See Solution 20952).
- Make sure there is enough disk space available where the temp tables are being created. You can specify the location with the environment variable WRKDIR (See Solution 19920).
- Do not use ORDER BY if not needed. Depending on the query plan, it might create huge temp tables for sorting.
- If you are hitting the 500MB limit for the temp tables (See Solution 21206), then increase the limit by:
1) specifying the TPE_MM_SWAPSIZE environment variable if you are using Progress 9.1D05 or earlier. The default value is 500000 (500MB).
Note: This method should be used as last resort as there is limited support for it. The database needs to be restarted to take this change into account.
- OR -
2) specifying the -SQLTempDisk database startup parameter if you are using Progress 9.1D06 or later. The default value is 500000 (500MB).