DBSQL_SQL_ERROR dumps in the system

[Former Member]

Hi,

We've been frequently receiving the below dumps in our production system from last 2-3 days and users are not able to perform any task and we're near to downtime,

Category               Installation Errors

Runtime Errors         DBSQL_SQL_ERROR

Except.                CX_SY_OPEN_SQL_DB

Date and Time          10.04.2016 10:19:23

Short Text

     SQL error "SQL code: 2843" occurred while accessing table "VBERROR".

What happened?

     Database error text: "SQL message: [ASE Error SQL2843][SAP][ASE ODBC

      Driver][Adaptive Server Enterprise]The dynamic statement 'J4' could not be

      found. This may be due to insufficient metadata descriptors. Increase the

      serverwide 'number of open objects' parameter or the 'procedure cache size' parameter."

Our production landscape environment is as below,

  - SAP ERP 6.0 EHP7 (Kernel 7.42 PL300)

  - SAP ASE 15.7 SP132

  - Windows Server 2012 R2

  - 2 nodes in Microsoft Failover Clustering with additional 2 application servers

Surprisingly we haven't received this error (not even once) in any of the other systems (Sandbox, Development, Quality, Training & Pre-production) in the landscape. All are on the same packages level and kernel version.

We've raised a support ticket with SAP and first thing they asked was to increase the "open objects" parameter by 30% (previously 60000) and send the sp_monitorconfig 'all' output and sybdiag zip file.

We did it and the next thing they asked was to increase the "open partitions" parameter to 80000 (previously 50000).

We did that also but the issue is not resolved yet. SAP again asked for sp_monitorconfig output and we've send it and waiting for response...

I am not an expert in ASE but from the below output of sp_monitorconfig 'all', one could easily make out that the "max memory", "open indexes" and "procedure cache" is hitting 100% percent.

Does it has to do anything with the issue?

P.S.: No parameters were changed except for "open objects", "open partitions", "max memory" and "heap memory per user". Also I've complied with the SAP note 1749935 - SYB: Configuration Guide for SAP ASE 15.7 and executed SAP Configuration Check from DBA Cockpit and couldn't find anything amiss.

Name                      Num_free    Num_active  Pct_act Max_Used    Reuse_cnt   Instance_Name

------------------------- ----------- ----------- ------- ----------- ----------- --------------

additional network memory    10485760           0   0.00            0           0 NULL

audit queue size                  100           0   0.00            0           0 NULL

compression info pool siz       27296        5472  16.70         5903           0 NULL

disk i/o structures              4096           0   0.00            0           0 NULL

heap memory per user            57732        7804  11.91         9170           0 NULL

kernel resource memory          28200        4568  13.94         4568           0 NULL

max cis remote connection        1200           0   0.00            0           0 NULL

max memory                          0     4096000 100.00      4096000           0 NULL

max number network listen           4           1  20.00            1           0 NULL

max online engines                 15           1   6.25            1           0 NULL

memory per worker process        1024           0   0.00            0           0 NULL

number of alarms                  984          16   1.60           19           0 NULL

number of aux scan descri        1024           0   0.00          103           0 NULL

number of devices                 180          20  10.00           20           0 NULL

number of dtx participant         500           0   0.00            0           0 NULL

number of java sockets            141         176  55.52          200           0 NULL

number of large i/o buffe          32           0   0.00            1           0 NULL

number of locks               4999521         479   0.01       760070           0 NULL

number of mailboxes                29           1   3.33            1           0 NULL

number of messages                 64           0   0.00            0           0 NULL

number of open databases            3           9  75.00            9           0 NULL

number of open indexes              1       59999 100.00        60000           0 NULL

number of open objects          41476       36524  46.83        36527           0 NULL

number of open partitions       20001       59999  75.00        60000       44206 NULL

number of remote connecti          20           0   0.00            4           0 NULL

number of remote logins            20           0   0.00            4           0 NULL

number of remote sites             10           0   0.00            1           0 NULL

number of sort buffers            308         192  38.40          500           0 NULL

number of user connection         126         174  58.00          216           0 NULL

number of worker processe           0           0   0.00            0           0 NULL

partition groups                 1024           0   0.00            0           0 NULL

permission cache entries           80          48  37.50           64        6823 NULL

procedure cache size             2297      259847  99.12       262144       54949 NULL

size of global fixed heap         600           0   0.00            0           0 NULL

size of process object he        9000           0   0.00            0           0 NULL

size of shared class heap       12288           0   0.00            0           0 NULL

size of unilib cache           523184        1104   0.21         1104           0 NULL

txn to pss ratio                 4800           0   0.00            0           0 NULL

[victoria_normand]

Hi Muzzafar,
Your procedure cache should be increased, 262144 is the minimum size recommended, along with the statement cache size (portion of the procedure cache reserved for cached query plans), that we use to recommend to have it sized with half of the total of procedure cache. You may refer to the .pdf document attached to SAP Note 1680803 - SYB: SAP Adaptive Server Enterprise - Best Practice for SAP Business Suite and SAP BW on page 38 you have a table with some configuration size suggestions.

Kind regards,
Victoria.