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 : - 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
Resolution
- Modify SQLANYs_SQLAnywhereForBI startup
- 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
- Take a backup copy of BI4_Audit.db, BI4_Audit.log files
- Stop all BOBJ services connected to CMS and Audit databases
- 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; - Rebuild SAP Sql Anywhere database (SAP Note : 2135940 )
- Replace old BI4_Audit.db, BI4_Audit.log with a newly rebuilt database
- 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