This section contains error messages for the Adaptive Server Memory Manager.
19
There is not enough procedure cache to run this procedure, trigger, or SQL batch. Retry later, or ask your SA to reconfigure SQL Server with more procedure cache.
This error occurs when there is not enough procedure cache available to execute a stored procedure, trigger, or batch of one or more SQL statements. This can happen when loading a query plan into procedure cache or when resolving or compiling a procedure, trigger, or batch.
The amount of available procedure cache is based on Adaptive Server use, so this error may occur intermittently as procedure cache is used and released. Also, one or more procedures can repeatedly fail with this error while others complete normally. This is because query plans vary greatly in size. Therefore, while there may not be sufficient space to load a large query plan, several smaller plans may fit.
Index creation uses sort buffers to hold data during sort operations. Each sort buffer reserves procedure cache, in proportion to the number of rows on the data page in the buffer; if the data rows are short, there are many rows per page, requiring more cache space to be allocated per page. This factor, multiplied by the number of sort buffers needed, can also exhaust procedure cache leading to the 701 error. For corrective action in this scenario, see “701 Error During Index Creation” below.
Error 701 is raised with the following states:
State |
Meaning |
|---|---|
1 |
Unable to read a procedure into cache from sysprocedures. |
2 |
Unable to allocate memory for a procedure header. |
3 |
Unable to find sufficient space to accomodate the query in the current procedure header. |
4 |
Unable to allocate memory for a procedure header based on existing page allocation. |
5 |
Unable to allocate space from kernel memory. |
7 |
Unable to write a procedure from cache into sysprocedures. |
8 |
Similar to State 1. |
9 |
Unable to allocate memory during a log scan. |
Version 12.5 and Later
Correct this error by increasing the configuration parameter procedure cache size to make more procedure cache available.
If the error is seen during index creation, see “701 Error During Index Creation” below for corrective action.
Version 12.0.x and Earlier
Correct this error by increasing the size of procedure cache. You can increase the amount of total procedure cache in three ways:
Use the total memory configuration parameter to increase the total amount of memory requested by Adaptive Server.
Use the procedure cache percent configuration parameter to increase the percentage of total cache space dedicated to procedure cache.
Decrease the amount of memory required for other resources; for example, unneeded user connections.
Increasing the total memory configuration parameter for Adaptive Server is the most straightforward method. It increases both the procedure and data caches, although it can waste memory.
On some operating systems, especially VMS and most UNIX
systems, be sure to make any necessary operating system memory resource
adjustments. For example, verify the current kernel value for the
maximum size of a shared memory segment, usually SHMMAX,
and make sure you have adequate memory and swap space on the system
for the additional memory configured for the Server. For more information,
refer to a) your operating system documentation, and b) the Adaptive
Server installation and configuration guide.
If your memory resources are limited, you can increase the procedure cache percent configuration parameter for the Adaptive Server without changing the value of the total memory configuration parameter. This shifts space from the data cache to the procedure cache and may result in performance degradation or other problems if not enough data cache remains.
You can also increase the size of the procedure cache without substantially changing the amount of data cache. This requires some combination of increasing both the total memory and procedure cache percent configuration parameters in such a way that the majority of the new memory goes to procedure cache. Although this method requires more planning, it allows you to control where the additional memory goes. Refer to the Performance and Tuning Guide and “Configuring Memory” in the System Administration Guide for more information about configuring Adaptive Server memory.
If the 701 error is raised when creating an index, you can:
increase procedure cache, or
decrease the number of sort buffers using sp_configure, or
decrease the number of rows per page using max_rows_per_page, or by increasing the row size with dummy columns.
All versions
20
Memory request for %d bytes exceeds the size of single page of %d bytes.
This error occurs when you exceed any of the following limits:
128 search conditions or join operations in a SQL statement.
A search condition sets the conditions in a where or having clause. For more information about, and examples, of search conditions, refer to “Search Conditions” in the Reference Manual.
A join operator compares two or more tables or views. For more information about and examples of join operators, refer to the Reference Manual.
128 columns in a Data Workbench update or delete table.
When modifying database tables, Data Workbench sends every updated table column as a search condition to Adaptive Server. Therefore, any Data Workbench table that has more than 128 columns and is updated though the “Modify Data” option generates Error 702.
The total width of the columns in a temporary table during a union exceeds the allowed row width (on a server of 2K page size, rows consist of 1962 characters; allow 2 characters of row overhead for APL tables, for a maximum usable row size of 1960. For DOL tables, subtract two characters per varchar column in determining usable row size).
After displaying Error 702, Adaptive Server terminates the current process.
If your query exceeds the limit of 128 search conditions or join operations, rewrite the query so that the limit of 128 statements is not exceeded.
If the total width of the columns in a temporary table during a union exceeds the allowed row width, rewrite your query so that the limit (1962 bytes on a 2K page server) is not exceeded.
If a table has more than 128 columns, avoid updating or deleting rows from that table via the “Modify Data” option of Data Workbench. Instead, run queries using SQL statements and take into consideration the primary, unique keys on that table when you define the rows that are to be updated or deleted.
For example, if a table has a unique key on column column1, run the following query in order to delete the row in the table that contains the unique key unique_key1:
1> begin transaction 2> delete table_name 3> where column1 = unique_key1 4> go
If the key is unique in the table, only one row will be deleted by the above query. If only one row is being returned by the above query, commit the transaction with the following query:
1> commit transaction 2> go
Otherwise, roll back the transaction:
1> rollback transaction 2> go
If you are not sure if the key unique_key1 is unique in column1, you can check by running the following query:
1> select * from table_name 2> where column1 = unique_key1 3> go
Similarly, if the table has a unique index on columns column1 and column2, you can delete a row in that table by running the following query:
1> delete table_name 2> where column1 = unique_key1 3> and column2 = unique_key2 4> go
All versions
17
You cannot run this procedure, trigger, or SQL batch because it requires more than %ld pages of memory. Break it up into shorter queries, if possible.
This error occurs when a stored procedure or trigger cannot be executed because it requires more memory than is allowed for execution.
Two different stages of execution can trigger this error: “resolution,” in which the query tree is built, or “ compilation,” in which the query plan is generated. If a query has been executed successfully but later fails with this error, it means that the query tree used to be less than the allowed memory limit but has since grown. If a query encounters this error every time it is executed, then it is too complex to be executed in its present form without exceeding the memory limit. Refer to the Performance and Tuning Guide for detailed information about query trees and query plans.
Errors 701 and 703 are very similar. Error 703 is raised
when the procedure requires multiple process headers (control structures)
but there is not enough memory to allocate the next header; Error
701 is raised when there is not enough memory to extend the current
header which already has some pages allocated. Refer to Error 701 for additional
troubleshooting information.
If the procedure or trigger causes this error every time you try to execute it, divide it into smaller pieces. This division process varies greatly with the type of SQL statement.
If the object has successfully executed before, the 703 error probably occurred because the query tree grew beyond the specified page limit. This growth occurs each time the query tree is re-resolved. Once you have encountered the 703 error in this situation, you can drop and re-create the procedure or trigger in order to shrink the query tree to a legal size, or you can break the object up into smaller modules. Restarting Adaptive Server has no effect on the size of the query tree because query trees are stored on disk.
If this error occurs frequently on an object, you can either periodically drop and re-create the object as part of regular database maintenance, or break it into smaller modules.
All versions
20
Process %d tried to remove PROC_HDR 0x%lx that it does not hold in Pss.
The Memory Manager allocates, deallocates, and manages memory for Adaptive Server. It manages an array of structures in the procedure header, each of which represents a physical page of memory in procedure cache and the current byte allocation of that page.
As part of its memory deallocation process, Adaptive Server stops tracking procedure headers when they are no longer needed and attempts to remove them from procedure cache. Error 706 occurs when Adaptive Server fails to deallocate a procedure header.
Error 706 is caused by memory corruption or an Adaptive Server problem.
Since procedure headers are stored in procedure cache, restarting Adaptive Server should clear Error 706. If it does not clear the 706 error or if the 706 error occurs again, contact Sybase Technical Support.
All versions
20
System error detected during attempt to free memory at address 0x%lx. Please consult the SQL Server error log for more details.
The Memory Manager allocates, deallocates, and manages memory for Adaptive Server. It manages an array of structures in the procedure header, each of which represents a physical page of memory in procedure cache and the current byte allocation of that page.
As part of its memory deallocation process, Adaptive Server tries to release the pages of memory allocated to a procedure header when they are no longer needed. When Adaptive Server is unable to release that section of memory, Error 707 occurs.
Error 707 is caused by memory corruption or an Adaptive Server problem.
Since procedure headers are stored in procedure cache, restarting Adaptive Server should clear Error 707. If it does not clear the 707 error or if the 707 error occurs again, contact Sybase Technical Support.
All versions
17
There is insufficient system memory to continue login process for spid %d.
The Memory Manager allocates and deallocates memory for Adaptive Server processes and manages memory requirements for the system. For each process connecting to the server, the Memory Manager allocates a memory structure called a procedure header and ensures that there is enough memory to dedicate to the process.
Error 709 is raised when a user process attempts to log in to the server, but there is not enough memory available to complete the login.
Correct this error by increasing the amount of available memory. You can do this in three ways:
Use the total memory configuration parameter to increase the total amount of memory requested by Adaptive Server.
Use the procedure cache percent configuration parameter to increase the percentage of total cache space dedicated to procedure cache.
Decrease the amount of memory required for other resources; for example, unneeded user connections.
Increasing the total memory configuration parameter for Adaptive Server is the most straightforward method. It increases both the procedure and data caches, although it can waste memory.
Since total memory and procedure cache percent are static parameters, you must restart Adaptive Server after changing these parameters.
Check the value of the number of user connections configuration parameter. Since there is memory overhead associated with each user connection, setting this parameter too high can reduce the amount of space available for the data and procedure caches, and contribute to 709 errors.
All versions
