SAP Knowledge Base Article - Public

2468613 - High memory usage by dbsrv12.exe (dbsrv16.exe) process running Business Objects BI4_Audit database

Symptom

  • SAP SQL Anywhere server running Business Objects CMS and Audit database is showing high memory consumption
  • dbsrv12.exe (dbsrv16.exe) process utilizing high percentage (e.g. 20Gb or >70% depending on hardware) of the total memory
  • BI4_Audit.db database size has grown over 20 of gigabytes

Environment

  • BusinessObjects Enterprise XI 4.0
  • BusinessObjects Enterprise XI 4.1
  • BusinessObjects Enterprise XI 4.2
  • SAP SQL Anywhere 12
  • SAP SQL Anywhere 16

Cause

  • SAP SQL Anywhere on startup (if no "-c" option is specified ) will set initial cache depending on database size and operating system
    Please see default cache setting for :
  • SAP SQL Anywhere server periodically evaluates current cache size and increases and decreses it depending on demand
    See: Dynamic cache sizing

Resolution

  • Modify SQLANYs_SQLAnywhereForBI startup
    1. Set SQL Anywhere option and set maximum cache size limit with "-ch <size>" option
      See : -ch database server option

      Note: running database server with non-optimal cache can impact performance.

  • Archive data from BI4_Audit database
    1. Take a backup copy of BI4_Audit.db, BI4_Audit.log files
    2. Stop all BOBJ services connected to CMS and Audit databases
    3. Archive entries in ADS_EVENT or ADS_EVENT_DETAIL (SAP Note : 2257580 )

      Connect to BI4_Audit database using dbisql and delete top 1000000 rows
      from ADS_EVENT table prior to the Start_Time

      E.g. execute until no more rows are deleted :

      DELETE FROM ADS_EVENT WHERE Event_ID IN
       (
       SELECT TOP 1000000 Event_ID FROM ADS_EVENT WHERE
       Cluster_ID = 'CUID of the Cluster' AND
       Start_Time < '2015-08-01 00:00:00'
       ORDER BY Event_ID
       );
      COMMIT;


    4. Rebuild SAP Sql Anywhere database (SAP Note : 2135940 )
    5. Replace old BI4_Audit.db, BI4_Audit.log with a newly rebuilt database
    6. Restart SQLANYs_SQLAnywhereForBI service and all BOBJ services.

Keywords

BI4_CMS.db, BI4_Audit.db, BI, BOBJ, archive, deleting, cache , KBA , BC-SYB-SQA , SQL Anywhere (on premise, on demand) , BI-BIP-SRV , CMS / Auditing issues (excl. 3rd Party Authentication) , Problem

Product

SAP BusinessObjects Dashboards 4.0, feature pack 3