"Unable to start the database - BI4_CMS.log is not a valid transaction log"

[Former Member]

Hi friends,

My server Intelligence Agent (SIA) can not start because the database service "SQLAnywhereForBI" can't start also. Inside the database log file i got the following error :

"Unable to start the database - BI4_CMS.log is not a valid transaction log"

Can you help me plz

[Former Member · Accepted solution]

I found the solution by following the advice given on the following forum:

http://evtechnologies.com/transaction-logs-on-sybase-sql-anywhere-and-sap-​​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

[jeff_albion · Accepted solution]

Hello Abdelhak,

There is a problem with running the above instructions by turning off the transaction log, namely that the performance of the SQL Anywhere database will suffer:

Tip: Always use a transaction log        

Using a transaction log can provide data protection, and can dramatically improve the performance of SQL Anywhere.

When operating without a transaction log, SQL Anywhere performs a checkpoint at the end of every transaction which consumes considerable resources.

When operating with a transaction log, SQL Anywhere only writes notes detailing the changes as they occur. It can choose to write the new database pages all at once, at the most efficient time. Checkpoints make sure information enters the database file, and that it is consistent and up to date.

You can further improve performance if you store the transaction log on a different physical device than the one containing the primary database file. The extra drive head does not generally have to seek to get to the end of the transaction log.

Hence this command will slow down all BI operations in production and is ***NOT RECOMMENDED***.

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

It also eliminates a possible avenue of database recovery if your database file is ever corrupted - see below.

---

What you should have originally done is run a regular backup of your SQL Anywhere database (See: ) and restored your backup when you encountered the following corruption of the transaction log file:

"Unable to start the database - BI4_CMS.log is not a valid transaction log"

The article that you have linked does contain some correct information, in that if you absolutely need to shrink the size of your transaction log file immediately, you can use a transaction log backup statement:

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

However, the linked article is incorrect in that it should instead encourage regular database backups and transaction log incremental backups rather than just turning off the transaction log entirely. With a proper backup plan, you only need to keep as many transaction logs that have been generated since your last full database backup (e.g. "Incremental Backup"). If your database is very busy, you should design a schedule that optimizes the time to backup, and the amount of space that is required to store all of the backup pieces.

From the above article:

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.

If this is the workload demand, then the answer is to run the backup statement more often, like in a maintanence plan. Creating a full backup more often may be prudent if the database size is relatively small but the transaction log growth is high.

Regards,

Jeff Albion

SAP Active Global Support