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

• 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

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

• 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 :
• version 12 (  -c dbsrv12 )
• version 16 ( -c dbsrv16 )
• SAP SQL Anywhere server periodically evaluates current cache size and increases and decreses it depending on demand
  See: Dynamic cache sizing

• 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.

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