SAP

How to fix ASE Error SQL701-There is not enough procedure cache to run this.

by

Discover how to tackle ASE Error SQL701, a common challenge in SQL Server performance optimization. Learn effective troubleshooting techniques to resolve procedure cache shortages and ensure your database runs smoothly.

Symptom

  • You see this ABAP dump error reported frequently from ST22:
    Category Installation Errors
    Runtime Errors DBIF_RSQL_SQL_ERROR
    Except. CX_SY_OPEN_SQL_DB
    Date and Time <timestamp>


Short text
SQL error 701 when accessing table “<tablename>”.

What happened?
Error Text of the Database: “[ASE Error SQL701]There is not enough procedure
cache to run this procedure, trigger, or SQL batch. Retry later, or ask your
SA to reconfigure ASE with more procedure cache. “

  • You should see similar messages like this in the ASE errorlog:
    Error: 701, Severity: 17, State: 12
    There is not enough procedure cache to run this procedure, trigger, or SQL batch. Retry later or ask your SA to reconfigure ASE with more procedure cache.

Environment

  • SAP Adaptive Server Enterprise (ASE) 15.7 and 16.0 for Business Suite
  • SAP NetWeaver (NW) – All versions 

Cause

There is a heavily used procedure cache in ASE. When you rerun your transaction, this might succeed since the procedure cache is not fully used all the time.

Resolution

  • If your ASE version is ASE 16.0 SP03 PL10 or ASE 16.0 SP04 PL01, directly refer to Resolution of SAP Note 3114871 – “SYB: Severe performance degradation after upgrade of SAP ASE”
  • If your ASE version is lower than ASE 16.0 SP03 PL10 or is ASE 16.0 SP04 PL00 then:
  1. Monitor the ‘procedure cache size’ max usage by using one of those 2 methods:
    • Using command sp_monitorconfig ‘procedure cache size’ , when the error 701 is reported the usage should be 100%, and the reuse counter reports a high number.
    • Example:
      isql -S<SID> -Usapsa -P<password> -X

      1> sp_monitorconfig 'procedure cache size'
      2> go

Usage information at date and time: <timestamp>.

 Name                      Num_free    Num_active  Pct_act Max_Used    Reuse_cnt   Instance_Name
 ————————- ———– ———– ——- ———– ———– ——————————
 procedure cache size           185526      652589  77.86       838115      256527 NULL

  • Or using DBACOCKPIT -> Database <SID> tab -> Menu Performance -> Resource Utilization.

2. If error 701 persists, increase ASE configuration parameter “procedure cache size“:

  • Go to DBACOCKPIT -> Menu Configuration -> Server Configuration -> localize “procedure cache size” under “Memory Use” Group -> Click on “Change” button and enter New Value.
ASE Error SQL701
  • You can also do the change manually by running this command on an isql tool command line:

    isql -S<SID> -Usapsa -P<password> -X

    sp_configure 'procedure cache size', <new value>
    go
  • Make sure that you have enough memory specified for the ASE, so the new value fits into it.
  1. Ensure the minimum recommended configuration value for “procedure cache size” is the one recommended, please check the SAP Note 1680803 – “SYB: SAP Adaptive Server Enterprise – Best Practice for SAP Business Suite and SAP BW”  > Chapter 5. BASIC ASE RUNTIME CONFIGURATION > b. ASE Sizing

Leave a Comment