17
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 the procedure cache is not large enough 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 the procedure cache content 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 raised during index creation”.
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. |
10 |
Unable to allocate memory for a sort. |
11 |
Unable to allocate memory for a procedure header. |
12 |
Unable to allocate memory for statement cache. |
13 |
Unable to allocate memory during a dump transaction command using the "with standby access" option. |
14 |
Unable to allocate memory for parsing client SQL. |
15 |
Unable to allocate memory for a bitstring operation. |
16 |
Unable to allocate memory for a bitstring operation. |
17, 18, 19 |
Used only by the Adaptive Server Enterprise exerciser. |
20 |
Unable to allocate memory during an alter table. |
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 the title “701 error during index creation” in this section for corrective action.
Version pre-12.5
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 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. See the sections entitled “Memory Use and Performance” in Performance and Tuning: Basics and “Configuring Memory” in System Administration Guide: Volume 2 for more information about configuring Adaptive Server memory.
If 701 error is raised when creating an index:
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
