Transaction Logs on Sybase SQL Anywhere and SAP BusinessObjects BI 4.1

Dallas Marks recently posted a great primer for customers tinkering with SAP BusinessObjects BI4.1 running on the default CMS and Audit database, SQL Anywhere (see related article, Getting Somewhere with Sybase SQL Anywhere and SAP BusinessObjects BI 4.1). He uncovered great information for getting acquainted with SQL Anywhere, querying it, scaling it for a multi-node cluster and more.  I wanted to title this post “Getting Nowhere with Sybase SQL Anywhere and SAP BusinessObjects” in response but SEO sensibilities got ahold of me.

In our lab at EV Technologies, we also directed monitoring and auditing to use Sybase SQL Anywhere.  Today we started the day to a fun surprise that the disk on our lab machine was completely full and the CMS had come to a stop.  Using the handy utility WinDirStat, I drilled down a few directories to find the SQL Anywhere directory where the database files were stored (by default C:\Program Files (x86)\SAP BusinessObjects\sqlanywhere\database) was over 110 GB on a two-week old SAP BusinessObjects BI 4.1 SP2 install.  More specifically, the log file for the Auditor database was over 110 GB.

A quick search educated me that like other major database vendors, SQL Anywhere has a transaction log.  In a common theme, the transaction log is the history of all inserts, updates, and deletes to the database.  Since monitoring and auditing are so verbose, they blew our transaction log as configured out of the box with SAP BusinessObjects BI 4.1 out of the water.

The fast solution was a bandaid.  Another quick search and the SQL command (run through the installed Interactive SQL client) and the log file was blasted from the machine, instantly freeing the 110 GB.  Note: this command was executed while logged on to an ODBC connection to the Auditor database.

backup database directory '' transaction log only transaction log truncate;

This definitely solved the issue in a tactical approach.  However, upon closer inspection a few hours later, the transaction log had already grown to 3+ GB on a lab machine.

The only real way to squash this is to stop SQL Anywhere from retaining so much transaction log data.  Is that good for your database?  Don’t get hung up on that yet.  I’m going to address that in a few.

First, get a command prompt going as administrator of your machine and run the following command, where the user ID and password reflect that of your installation.

dbstop -d -c "links=tcpip;eng=BI4;DBN=BI4_Audit;uid=dba;pwd=mypass"

This takes the database offline.  Next, I’m going to turn off the transaction log on my Auditor database.  I don’t really need it. To run this, connect to the CMS database (not the Audit database, as we just took it offline) via Interactive SQL and run the command (this can also be done via command line with the dbisql command I do believe, but this works as well.

alter database 'PATH_TO_YOUR_DB_FILE\BI4_Audit.db' alter log off;

For grins, I bounced the SQL Anywhere for SAP Business Intelligence service here to continue and now, no transaction log.

To bring this post to a close, I want to unflinchingly tell you that you shouldn’t be using Sybase SQL Anywhere in SAP BusinessObjects production or any other SAP BusinessObjects landscape that is very important to you on an unmanaged SQL Anywhere database.  It’s not a best practice.  You should partner with your nice DBAs to get well-managed databases for your SAP BusinessObjects landscapes.  Can that be Sybase SQL Anywhere?  Sure.  But if you are going to use SQL Anywhere Rambo-style (I know you are out there), be sure to start brushing up on your Sybase DBA skills to head off potential issues such as this.  It only took two days in our lab after I blasted the file the first time for it to creep back to 35 GB of storage.

edited Feb 3, 2014 12:56 to indicate my conclusions that this article is for SAP BusinessObjects customers specifically.