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.

Screen Shot 2014-01-13 at 8.41.18 PM

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.

5 thoughts on “Transaction Logs on Sybase SQL Anywhere and SAP BusinessObjects BI 4.1

  1. Hi friend,

    I have the same problem with you, and i couldn’t find a solution and run the command alter database mentionned. It’s because I couldn’t connect to the CMS database, i think i forget the id and the password of sybase sql anywhere.

    Is there a solution for me
    Thanks a lot

      1. Hi Eric,
        Thank you for the answer,
        fortunately I succeeded to connect to the CMS database with DBA user and password in the SQL Intractive. But when I run the command Alter database ‘C: \ Program Files (x86) \ SAP BusinessObjects \ sqlanywhere \ database \ BI4_Audit.db’ alter log off, I get the following error:

        Could not execute statement.
        Database “C: \ Program Files (x86) \ SAP
        BusinessObjects \ sqlanywhere \ database \ BI4_Audit.db “needs recovery
        SQLCODE = -665, ODBC 3 State = “HY000”
        Line 1, column 1

        Is there a solution without making the database restoration for BI4_Audit.db. Because I don’t have a restore point for this database?

        thank you very much

      2. I found the solution by following the advice given on the following forum:​​businessobjects-bi-4-1

        In fact, I crushed the BI4_Audit.db and BI4_Audit.log files and I replaced with others that I got from another machine where I installed BO again and where the files are not corrupted . After I logged in to the CMS database by executing the command in the command line:
        dbisql -c “UID = DBA; PWD = mypassword; BI4 Server =; DBF = C: \ Program Files (x86) \ SAP BusinessObjects \ sqlanywhere \ database \ BI4_CMS.db.”

        Once connected, I start the command:

        alter database ‘C: \ Program Files (x86) \ SAP BusinessObjects \ sqlanywhere \ database \ BI4_Audit.db’ alter log off;

        The query runs successfully.
        And that’s good, I can be connected to BO smoothly.

        Thank you again Eric

    1. j’ai trouvé la solution en suivant les conseils donnés sur le forum suivant:

      En fait, j’ai écrasé les fichiers BI4_Audit.db et BI4_Audit.log et je les remplacé avec d’autres que j’ai récupéré d’une autre machine où j’ai installé BO de nouveau et où les fichiers ne sont pas endommagés. Après je me suis connécté à la base CMS en executant la commande dans la ligne de commande :
      dbisql -c “UID=DBA;PWD=mypassword;Server=BI4;DBF=C:\Program Files (x86)\SAP BusinessObjects\sqlanywhere\database\BI4_CMS.db” .

      Une fois connécté, j’ai lancé la commande :

      alter database ‘C:\Program Files (x86)\SAP BusinessObjects\sqlanywhere\database\BI4_Audit.db’ alter log off;

      la requéte s’execute avec succes .
      Et c’est bon, je peux se connécté à BO sans problème.

      Merci encore Eric

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.